- Categories:
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.
- See also:
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
andstart
must be non-negative integer constants.count
specifies the number of rows returned.The values NULL, empty string (
''
), and$$$$
are also accepted and are treated as “unlimited”; this is useful primarily for connectors and drivers (such as the JDBC driver) if they receive an incomplete parameter list when dynamically binding parameters to a statement.OFFSET start
optionally specifies the row number after which the limited/fetched rows are returned. IfOFFSET
is omitted, the output starts from the first row in the result set.The values NULL, empty string (
''
) and$$$$
are also accepted and are treated as 0 (i.e. do not skip any rows); this is useful primarily for connectors and drivers (such as the JDBC driver) if they receive an incomplete parameter list when dynamically binding parameters to a statement.
ONLY
This optional keyword does not affect the output. It is merely for emphasis to the human reader.TOP <n>
andLIMIT <count>
are equivalent.
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 | +----+
The following example demonstrates the use of NULLs to indicate
No limit to the number of rows.
Start at row 1 (do not skip any rows)
CREATE TABLE demo1 (i INTEGER); INSERT INTO demo1 (i) VALUES (1), (2);
SELECT * FROM demo1 ORDER BY i LIMIT NULL OFFSET NULL; +---+ | I | |---| | 1 | | 2 | +---+
SELECT * FROM demo1 ORDER BY i LIMIT '' OFFSET ''; +---+ | I | |---| | 1 | | 2 | +---+
SELECT * FROM demo1 ORDER BY i LIMIT $$$$ OFFSET $$$$; +---+ | I | |---| | 1 | | 2 | +---+