This is especially useful when wanting to paginate results in web applications – spreading them out over several pages by retrieving a subset of the database results.
Examples will use the following table and data:
Here’s how to limit the results to 2 records using LIMIT:
SELECT * FROM table_people LIMIT 2;
Which will return:
| id | name | |----|------| | 1 | Bob | | 2 | Ted |
You can also start the results at a specific position – skipping a set number of records before including records in the results.
This example skips the first three results and returns 2 results total:
SELECT * FROM table_people LIMIT 2 OFFSET 3;
| id | name | |----|------| | 4 | Pip | | 5 | Zak |
The OFFSET clause cannot be used independently of the LIMIT clause.
Combined LIMIT and OFFSET
The above query using both LIMIT and OFFSET can also be written as:
SELECT * FROM table_people LIMIT 3, 2;
This removes the need to type out OFFSET by including it in the LIMIT clause as the first parameter.
I prefer not to combine them, as having them displayed with both the words LIMIT and OFFSET makes it clearer what’s going on when skimming over your code.
LIMIT, OFFSET & Pagination
The most common usage for LIMIT and OFFSET is providing pagination.
This is pretty simple and works in any programming language:
- LIMIT in this scenario is the number of records you want to display per page
- OFFSET is where the records on the page start at
- So, if you are on page 3, OFFSET should be 3 * LIMIT.