SQL Limit and Offset clauses
The LIMIT clause specifies the maximum number of rows to return in the result set, while the OFFSET clause specifies the number of rows to skip before starting to return the result set.
The following shows the syntax of LIMIT & OFFSET clauses:
SELECT column_list... FROM table_name ORDER BY column_list LIMIT row_count OFFSET offset;
In the above syntax
LIMIT row_countdetermines the number of rows (row_count) returned by the query.
OFFSET offsetclause skips the offset number of rows before beginning to return the rows. (OFFSET clause is optional)
SELECT column1, column2, column3 FROM table_a LIMIT 10 OFFSET 20;
The above query returns 10 rows of column1, column2, column3 from table_a starting from 21st row.
ORDER BY clause is not mandatory when using LIMIT and OFFSET in a SQL query. However, it's a good practice to include an ORDER BY clause to ensure that the rows are returned in a consistent and predictable order.
To ensure that the predictable rows order include an ORDER BY clause:
SELECT column1, column2, column3 FROM table_a ORDER BY column1 LIMIT 10 OFFSET 20;
This query will return 10 rows starting from the 21st row of the table, ordered by the values in column1.
In MySQL, the above query has shorthand for limit and offset as mentioned below:
SELECT column1, column2, column3 FROM table_a ORDER BY column1 LIMIT 20, 10;
In the shorthand note that limit is followed by offset number and then row_count.
Best use-cases of Limit and Offset clauses
To get top N rows with the highest or lowest value
- You can use the
LIMITclause to get the top N rows with the highest or lowest value. For example, the following statement gets the top five students with the highest scores.
SELECT student_id, first_name, last_name, scores FROM Students ORDER BY scores DESC LIMIT 5;
Getting the row with the Nth highest value
- To get a student who has the 2nd highest score, you can use the
LIMIT OFFSETclauses as follows.
SELECT student_id, first_name, last_name, scores FROM Students ORDER BY scores DESC LIMIT 1 OFFSET 1;
LIMIT 1 returns one row whereas
OFFSET 1 skips the first highest score record thereby resulting in the second row from the result set.
Thank you, Happy learning.