LIMIT keyword

Specify the number and position of records returned by a SELECT statement.

Other implementations of SQL sometimes use clauses such as OFFSET or ROWNUM. Our implementation uses LIMIT for both the offset from start and limit.

Syntax​

SELECT ... LIMIT { numberOfRecords | lowerBound, upperBound };
  • numberOfRecords is the number of records to return.
  • upperBound and lowerBound is the range of records to return.

Here's the exhaustive list of supported combinations of arguments. m and n are positive numbers, and negative numbers are explicitly labeled -m and -n.

  • LIMIT n: take the first n records
  • LIMIT -n: take the last n records
  • LIMIT m, n: skip the first m records, then take up to record number n (inclusive)
    • result is the range of records (m, n] number 1 denoting the first record
    • if m > n, implicitly swap the arguments
    • PostgreSQL equivalent: OFFSET m LIMIT (n-m)
  • LIMIT -m, -n: take the last m records, then drop the last n records from that
    • result is the range of records [-m, -n), number -1 denoting the last record
    • if m < n, implicitly swap them
  • LIMIT m, -n: drop the first m and the last n records. This gives you the range (m, -n). These arguments will not be swapped.

These are additional edge-case variants:

  • LIMIT n, 0 = LIMIT 0, n = LIMIT n, = LIMIT , n = LIMIT n
  • LIMIT -n, 0 = LIMIT -n, = LIMIT -n

Null bounds​

A LIMIT bound can be null. Null bounds come from parameterized queries: a bind variable used as a bound (LIMIT $1, LIMIT $1, $2, or LIMIT :lo, :hi) that is bound to null or left unbound. A literal null written in the query text is rejected with invalid type: NULL.

In an ORDER BY ... LIMIT query:

  • LIMIT null: return all records (same as omitting LIMIT)
  • LIMIT null, n: take the first n records
  • LIMIT n, null: return no records (empty result)
note

Without ORDER BY, a null lower bound combined with a positive upper bound (LIMIT null, n) is rejected with LIMIT <negative>, <positive> is not allowed. LIMIT null and LIMIT n, null behave the same with or without ORDER BY.

Examples​

Examples use this schema and dataset:

CREATE TABLE orders (id LONG);
INSERT INTO orders VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
First 5 records
SELECT * FROM orders LIMIT 5;

 id
----
 1
 2
 3
 4
 5
Last 5 records
SELECT * FROM orders LIMIT -5;


 id
----
 6
 7
 8
 9
 10
Records 3, 4, and 5
SELECT * FROM orders LIMIT 2, 5;

 id
----
 3
 4
 5
Records -5 and -4
SELECT * FROM orders LIMIT -5, -3;

 id
----
 6
 7
Records 3, 4, ..., -3, -2
SELECT * FROM orders LIMIT 2, -1;

 id
----
 3
 4
 5
 6
 7
 8
 9
Implicit argument swap, records 3, 4, 5
SELECT * FROM orders LIMIT 5, 2;

 id
----
 3
 4
 5
Implicit argument swap, records -5 and -4
SELECT * FROM orders LIMIT -3, -5;

 id
----
 6
 7