SQL Limit and Offset clauses

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

• The LIMIT row_count determines the number of rows (row_count) returned by the query.
• The OFFSET offset clause skips the offset number of rows before beginning to return the rows. (OFFSET clause is optional)

Example

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.

Note: The 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 LIMIT clause 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 OFFSET clauses 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.