docs/en/sql-reference/statements/select/limit.md
The LIMIT clause controls how many rows are returned from your query results.
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 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.
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%) |
:::note
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 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.
:::note
WITH TIES is not supported with negative limits.
:::
This modifier can be combined with the ORDER BY ... WITH FILL modifier.
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.