Categories:
Query Syntax

LIMIT / FETCH

Constrains the maximum number of rows returned by a statement or subquery. Both LIMIT (Postgres syntax) and FETCH (ANSI syntax) are supported, and produce the same result.

Syntax

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

-- ANSI syntax
SELECT ...
FROM ...
[ ORDER BY ... ]
[ OFFSET <start> ] [ { ROW | ROWS } ] FETCH [ { FIRST | NEXT } ] <count> [ { ROW | ROWS } ] [ ONLY ]
[ ... ]

Usage Notes

  • An ORDER BY clause is not required; however, without an ORDER BY clause, the results are non-deterministic because results within a result set are not necessarily in any particular order. To control the results returned, use an ORDER BY clause.

  • count and start must be non-negative integer constants.

    • count specifies the number of rows returned.
    • OFFSET start optionally specifies the row number after which the limited/fetched rows are returned. If OFFSET is omitted, the output starts from the first row in the result set.
  • ONLY This optional keyword does not affect the output. It is merely for emphasis to the human reader.

Examples

The following examples show the effect of LIMIT. For simplicity, these queries omit the ORDER BY clause and assume that the output order is always the same as shown by the first query. Real-world queries should include ``ORDER BY``.

select c1 from testtable;

+------+
|   C1 |
|------|
|    1 |
|    2 |
|    3 |
|   20 |
|   19 |
|   18 |
|    1 |
|    2 |
|    3 |
|    4 |
| NULL |
|   30 |
| NULL |
+------+

select c1 from testtable limit 3 offset 3;

+----+
| C1 |
|----|
| 20 |
| 19 |
| 18 |
+----+

select c1 from testtable order by c1;

+------+
|   C1 |
|------|
|    1 |
|    1 |
|    2 |
|    2 |
|    3 |
|    3 |
|    4 |
|   18 |
|   19 |
|   20 |
|   30 |
| NULL |
| NULL |
+------+

select c1 from testtable order by c1 limit 3 offset 3;

+----+
| ID |
|----|
|  2 |
|  3 |
|  3 |
+----+