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 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:

Syntax	Result
`LIMIT -m`	Last `m` rows
`LIMIT -m OFFSET -n`	Last `m` rows after skipping the last `n` rows
`LIMIT m OFFSET -n`	First `m` rows after skipping the last `n` rows
`LIMIT -m OFFSET n`	Last `m` rows after skipping the first `n` rows

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:

Syntax	Result
`LIMIT 0.1`	First 10% of rows
`LIMIT 1 OFFSET 0.5`	The median row
`LIMIT 0.25 OFFSET 0.5`	Third quartile (25% of rows after skipping the first 50%)

Fractions must be Float64 values greater than 0 and less than 1.
Fractional row counts are rounded to the next whole number.

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: Stop before the first row 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. Without ALL, if the first UNTIL match appears before the first AFTER match, the result is empty. With AFTER ... ALL, later AFTER matches can still open new ranges. 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:

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

┌─number─┐
│      2 │
│      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. :::note

WITH TIES, fractional/negative LIMIT/OFFSET, and OFFSET are not supported together with AFTER/UNTIL.
Preliminary LIMIT pushdown is disabled when AFTER/UNTIL is used.
AFTER and UNTIL are recognized as keywords only when a boundary expression follows them, so an identifier named after or until still works as a row count (LIMIT after, LIMIT after BY x). When both readings are possible the keyword wins: LIMIT after(2) is the range LIMIT AFTER (2); write LIMIT (after(2)) to call a function named after. :::

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.

SQL Reference

Data Types

Engines

Functions

Formats

Interfaces

Settings

System Tables

Data Lakes

Basic syntax

Negative limits

Fractional limits

Combining limit types

LIMIT … WITH TIES

LIMIT … AFTER … UNTIL (range by conditions)

Considerations

See also

​Basic syntax

​Negative limits

​Fractional limits

​Combining limit types

​LIMIT … WITH TIES

​LIMIT … AFTER … UNTIL (range by conditions)

​Considerations

​See also

Basic syntax

Negative limits

Fractional limits

Combining limit types

LIMIT … WITH TIES

LIMIT … AFTER … UNTIL (range by conditions)

Considerations

See also