LIMIT Clause

Documentation for LIMIT Clause

LIMIT clause

The LIMIT clause controls how many rows are returned from your query results.

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.

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

LIMIT … WITH TIES

The WITH TIES modifier includes additional rows that have the same ORDER BY values as the last row in your limit.

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 is not supported with negative limits.

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

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.