LIMIT and OFFSET

Last modified: August 01, 2019

If want to LIMIT the number of results that are returned you can simply use the LIMIT command with a number of rows to LIMIT by.

SELECT * FROM artists LIMIT [Number to Limit By];

For example

SELECT * FROM artists LIMIT 3;

This ensures only the first 3 results are returned. Besides returning less results, LIMITing queries can greatly reduce the time they take to run and make your database administrator a lot less angry with you.

Give it a try by fetching yourself the first 6 rows of the artists table:

OFFSET

You can also specify an OFFSET from where to start returning data.

SELECT * FROM artists LIMIT 5 OFFSET [Number of rows to skip];

Say you want to get 5 artists, but not the first five. You want to get rows 3 through 8. You’ll want to add an OFFSET of 2 to skip the first two rows:

SELECT * FROM artists LIMIT 5 OFFSET 2;

Here’s a challenge for you. Write a query to fetch the Artists in rows 10 through 20:

Written by: Dave Fowler
Reviewed by: Matt David

Next – Browsing the SCHEMA

Get new data chapters sent right to your Inbox