LIMIT and OFFSET

Last modified: July 18, 2019

So far all of our queries have brought back all the rows in the table. That’s okay for our example database with just a few hundred rows of data, but on other databases that can often be A LOT more data than needed. If want to LIMIT the number of results that are returned you can simply use the LIMIT command at the end of the query to specify.

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

For example

SELECT * FROM artists LIMIT 3;

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