LIMIT

The LIMIT clause controls how many rows are returned from your query results. Rows can be selected by count and offset, or by the conditions that open and close a range of rows with LIMIT ... AFTER ... UNTIL.

Basic syntax

Select first rows:

LIMIT m

Returns the first m rows from the result, or all records when there are fewer than m.

Alternative TOP syntax (MS SQL Server compatible):

-- SELECT TOP number|percent column_name(s) FROM table_name
SELECT TOP 10 * FROM numbers(100);
SELECT TOP 0.1 * FROM numbers(100);

This is equivalent to LIMIT m and can be used for compatibility with Microsoft SQL Server queries.

Select with offset:

LIMIT m OFFSET n
-- or equivalently:
LIMIT n, m

Skips the first n rows, then returns the next m rows.

In both forms, n and m must be non-negative integers.

Select a range by conditions:

LIMIT [n] AFTER start_expr [UNTIL end_expr]
LIMIT [n] UNTIL end_expr

Returns the rows from the first row where start_expr is true, or from the start of the stream when AFTER is omitted, up to but excluding the first row at or after that start where end_expr is true; n caps the length of that range. AFTER start_expr ALL opens a range at every matching row. See LIMIT … AFTER … UNTIL below.

Negative limits

Select rows from the end of the result set using negative values:

The LIMIT -n, -m syntax is equivalent to LIMIT -m OFFSET -n.

Fractional limits

Use decimal values between 0 and 1 to select a percentage of rows:

Combining limit types

You can mix standard integers with fractional or negative offsets:

LIMIT 10 OFFSET 0.5    -- 10 rows starting from the halfway point
LIMIT 10 OFFSET -20    -- 10 rows after skipping the last 20

The range form combines only with a plain row count: LIMIT 3 AFTER start_expr takes at most three rows from where the range opens. OFFSET, fractional and negative counts, and WITH TIES are rejected together with AFTER or UNTIL. A LIMIT BY clause can precede a range in the same query, and the limit setting still caps the result.

LIMIT … WITH TIES

The WITH TIES modifier includes additional rows that have the same ORDER BY values as the last row in your limit. It applies to count and offset limits only and cannot be combined with the range form.

SELECT * FROM (
    SELECT number % 50 AS n FROM numbers(100)
) ORDER BY n LIMIT 0, 5

┌─n─┐
│ 0 │
│ 0 │
│ 1 │
│ 1 │
│ 2 │
└───┘

With WITH TIES, all rows matching the last value are included:

SELECT * FROM (
    SELECT number % 50 AS n FROM numbers(100)
) ORDER BY n LIMIT 0, 5 WITH TIES

┌─n─┐
│ 0 │
│ 0 │
│ 1 │
│ 1 │
│ 2 │
│ 2 │
└───┘

Row 6 is included because it has the same value (2) as row 5.

The same applies when the offset is specified with the OFFSET keyword:

SELECT * FROM (
    SELECT number % 50 AS n FROM numbers(100)
) ORDER BY n LIMIT 3 OFFSET 2 WITH TIES

┌─n─┐
│ 1 │
│ 1 │
│ 2 │
│ 2 │
└───┘

Skipping the first 2 rows and taking 3 would normally return 1, 1, 2, but the second 2 is included because it ties with the last row.

WITH TIES also works with negative limits and offsets. It includes additional rows that have the same ORDER BY values as the first selected row:

SELECT number % 3 AS n FROM numbers(15)
ORDER BY n LIMIT -4 OFFSET -3 WITH TIES

┌─n─┐
│ 1 │
│ 1 │
│ 1 │
│ 1 │
│ 1 │
│ 2 │
│ 2 │
└───┘

Without WITH TIES, the result would be 1, 1, 2, 2. With WITH TIES, three extra rows with value 1 are included because they tie with the first selected row.

This modifier can be combined with the ORDER BY ... WITH FILL modifier.

LIMIT … AFTER … UNTIL (range by conditions)

You can limit the result to a range of rows between two boundary conditions:

LIMIT [n] AFTER start_expr [UNTIL end_expr]
LIMIT [n] AFTER start_expr ALL [UNTIL end_expr]
LIMIT [n] UNTIL end_expr

• AFTER start_expr: Start output from the first row where start_expr is true (that row is included).
• AFTER start_expr ALL: Output the union of all matching ranges that start where start_expr is true, without duplicating rows when ranges overlap.
• UNTIL end_expr: End each range before the first row at or after its start where end_expr is true (that row is excluded).
• n: Optional row count. Without ALL it is the maximum length of the single opened range. With AFTER ... ALL it is the length of each opened range, so the total result can exceed n (for example, LIMIT 2 AFTER number IN (2, 6) ALL can return up to four rows). To cap the total number of result rows, use the limit setting, which is applied as a global limit after the range.

Stream order (the order rows are read) defines “first” match; use ORDER BY to control it.

UNTIL matches before a range starts have no effect. If both conditions match the starting row, the range is empty. If no UNTIL match occurs at or after the start, the range continues to its row count n or the end of the stream. With AFTER ... ALL, later AFTER matches can open new ranges after an earlier range ends.

With AFTER and without ALL, the range step evaluates AFTER until it finds a chunk containing a start match. It then evaluates UNTIL in that chunk and subsequent chunks while the range remains open. Expressions are evaluated over whole chunks, so UNTIL can still be evaluated for rows before the start within the starting chunk.

If UNTIL contains stateful functions such as rowNumberInAllBlocks, or functions that are non-deterministic within the query, it is evaluated from the first chunk to preserve those functions’ behavior. Without ALL, AFTER is evaluated only through the starting chunk; subsequent chunks evaluate only UNTIL. End matches before the start still have no effect.

Examples:

First 3 rows starting from the first row where number >= 3:

SELECT number FROM numbers(10) ORDER BY number LIMIT 3 AFTER number >= 3;

┌─number─┐
│      3 │
│      4 │
│      5 │
└────────┘

Rows from first row where number >= 2 until (exclusive) first row where number >= 6:

SELECT number FROM numbers(10) ORDER BY number LIMIT 10 AFTER number >= 2 UNTIL number >= 6;

┌─number─┐
│      2 │
│      3 │
│      4 │
│      5 │
└────────┘

Without n, all rows from the AFTER match to the end of the stream (or until UNTIL) are returned:

SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number >= 7;

┌─number─┐
│      7 │
│      8 │
│      9 │
└────────┘

Without n but with UNTIL, the range runs from the first AFTER match up to the first UNTIL match at or after it:

SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number >= 2 UNTIL number >= 6;

┌─number─┐
│      2 │
│      3 │
│      4 │
│      5 │
└────────┘

An UNTIL match before the start is ignored; here number = 1 has no effect, and the range ends before number = 6:

SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number = 3 UNTIL number IN (1, 6);

┌─number─┐
│      3 │
│      4 │
│      5 │
└────────┘

Emit 2 rows after every matching row, without duplicating overlaps:

SELECT number FROM numbers(10) ORDER BY number LIMIT 2 AFTER number IN (2, 3, 6) ALL;

┌─number─┐
│      2 │
│      3 │
│      4 │
│      6 │
│      7 │
└────────┘

With ALL and UNTIL, every opened range ends at its n rows or at the next UNTIL match, whichever comes first; here the range opened at 6 is cut by number = 7:

SELECT number FROM numbers(10) ORDER BY number LIMIT 2 AFTER number IN (2, 6) ALL UNTIL number = 7;

┌─number─┐
│      2 │
│      3 │
│      6 │
└────────┘

Without n, an UNTIL match closes the current range and a later AFTER match opens a new one, which runs to the end when no further UNTIL match follows:

SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number IN (2, 6) ALL UNTIL number = 4;

┌─number─┐
│      2 │
│      3 │
│      6 │
│      7 │
│      8 │
│      9 │
└────────┘

Without n and without UNTIL, every opened range runs to the end of the stream, so AFTER start_expr ALL returns the same rows as AFTER start_expr.

UNTIL alone returns the rows from the start of the stream up to the first row where the condition is true:

SELECT number FROM numbers(10) ORDER BY number LIMIT UNTIL number >= 3;

┌─number─┐
│      0 │
│      1 │
│      2 │
└────────┘

With n, UNTIL alone returns at most n rows from the start of the stream, still stopping at the first match:

SELECT number FROM numbers(10) ORDER BY number LIMIT 2 UNTIL number >= 3;

┌─number─┐
│      0 │
│      1 │
└────────┘

A range can follow LIMIT BY and then applies to the rows that LIMIT BY keeps:

SELECT number % 4 AS k, number FROM numbers(12) ORDER BY k, number LIMIT 2 BY k LIMIT 3 AFTER k >= 1;

┌─k─┬─number─┐
│ 1 │      1 │
│ 1 │      5 │
│ 2 │      2 │
└───┴────────┘

Considerations

Non-deterministic results: Without an ORDER BY clause, the rows returned may be arbitrary and vary between query executions.

Server-side limit: The number of rows returned can also be affected by the limit setting.

See also

• LIMIT BY — Limits rows per group of values, useful for getting top N results within each category.