LIMIT and OFFSET

Learn SQL

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:


If you're finding this SQL Tutorial helpful, you may also enjoy our product Chartio. Chartio is on a mission to enable anyone in a company with the power of a data analyst. To do so we've built a very user friendly interface for people to explore and visualize data with either SQL, or our drag and drop Visual SQL. You can connect to all of your different data sources, and quickly create visuals and dashboards to share with your team. Give us a spin, there's a 2 week free trial.