LIMIT and OFFSET
Last modified: December 09, 2019
LIMIT
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:
SELECT * FROM artists LIMIT 6;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