LIMIT and OFFSET clauses

LIMIT clause

The LIMIT clause is an output modifier. It is logically applied at the very end of the query. The LIMIT clause limits the number of output lines.

Note that although LIMIT can be used without the ORDER BY condition, in that case the results may not be deterministic. Nevertheless, this can be useful, for example when you want to get a quick slice of data.

OFFSET clause

The OFFSET clause specifies from which position to start reading values, i.e. the first OFFSET values are ignored.

Syntax

SELECT ...
FROM ...
[ ORDER BY ... ]
LIMIT <count> [ OFFSET <start> ]
[ ... ]

Parameters

  • <count>
    The number of rows to return. Must be a non-negative integer value.
    The value NULL is also accepted and treated as unrestricted.

  • OFFSET <start>
    The line number after which restricted/extracted lines are returned. Must be a non-negative integer value.
    If OFFSET is omitted, output starts at the first line in the result set.
    The value NULL is also accepted and treated as unrestricted (i.e. no rows will be skipped).

Examples

  • Let’s select the first 5 days from the table of days of the week:

    SELECT *
FROM weekdays
LIMIT 5;
    +--------+-----------+
| number |    name   |
+--------+-----------+
| 1      | Monday    |
+--------+-----------+
| 2      | Tuesday   |
+--------+-----------+
| 3      | Wednesday |
+--------+-----------+
| 4      | Thursday  |
+--------+-----------+
| 5      | Friday    |
+--------+-----------+

  • Let’s select 5 rows from the table of days of the week, starting from position 1 (i.e. ignoring the first row):

    SELECT *
FROM weekdays
LIMIT 5
OFFSET 1;
    +--------+-----------+
| number |    name   |
+--------+-----------+
| 2      | Tuesday   |
+--------+-----------+
| 3      | Wednesday |
+--------+-----------+
| 4      | Thursday  |
+--------+-----------+
| 5      | Friday    |
+--------+-----------+
| 6      | Saturday  |
+--------+-----------+