LIMIT

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%)

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

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.