> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for LIMIT Clause

# 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`](#limit-after-until).

<h2 id="basic-syntax">
  Basic syntax
</h2>

**Select first rows:**

```sql theme={null}
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):**

```sql theme={null}
-- 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:**

```sql theme={null}
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:**

```sql theme={null}
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](#limit-after-until) below.

<h2 id="negative-limits">
  Negative limits
</h2>

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

<h2 id="fractional-limits">
  Fractional limits
</h2>

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>
  * Fractions must be [Float64](/reference/data-types/float) values greater than 0 and less than 1.
  * Fractional row counts are rounded to the next whole number.
</Note>

<h2 id="combining-limit-types">
  Combining limit types
</h2>

You can mix standard integers with fractional or negative offsets:

```sql theme={null}
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](#limit-after-until) 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`](/reference/statements/select/limit-by) clause can precede a range in the same query, and the [`limit`](/reference/settings/session-settings/other#limit) setting still caps the result.

<h2 id="limit--with-ties-modifier">
  LIMIT ... WITH TIES
</h2>

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](#limit-after-until).

```sql theme={null}
SELECT * FROM (
    SELECT number % 50 AS n FROM numbers(100)
) ORDER BY n LIMIT 0, 5
```

```response theme={null}
┌─n─┐
│ 0 │
│ 0 │
│ 1 │
│ 1 │
│ 2 │
└───┘
```

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

```sql theme={null}
SELECT * FROM (
    SELECT number % 50 AS n FROM numbers(100)
) ORDER BY n LIMIT 0, 5 WITH TIES
```

```response theme={null}
┌─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:

```sql theme={null}
SELECT * FROM (
    SELECT number % 50 AS n FROM numbers(100)
) ORDER BY n LIMIT 3 OFFSET 2 WITH TIES
```

```response theme={null}
┌─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:

```sql theme={null}
SELECT number % 3 AS n FROM numbers(15)
ORDER BY n LIMIT -4 OFFSET -3 WITH TIES
```

```response theme={null}
┌─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`](/reference/statements/select/order-by#order-by-expr-with-fill-modifier) modifier.

<h2 id="limit-after-until">
  LIMIT ... AFTER ... UNTIL (range by conditions)
</h2>

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

```sql theme={null}
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`:

```sql theme={null}
SELECT number FROM numbers(10) ORDER BY number LIMIT 3 AFTER number >= 3;
```

```response theme={null}
┌─number─┐
│      3 │
│      4 │
│      5 │
└────────┘
```

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

```sql theme={null}
SELECT number FROM numbers(10) ORDER BY number LIMIT 10 AFTER number >= 2 UNTIL number >= 6;
```

```response theme={null}
┌─number─┐
│      2 │
│      3 │
│      4 │
│      5 │
└────────┘
```

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

```sql theme={null}
SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number >= 7;
```

```response theme={null}
┌─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:

```sql theme={null}
SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number >= 2 UNTIL number >= 6;
```

```response theme={null}
┌─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`:

```sql theme={null}
SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number = 3 UNTIL number IN (1, 6);
```

```response theme={null}
┌─number─┐
│      3 │
│      4 │
│      5 │
└────────┘
```

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

```sql theme={null}
SELECT number FROM numbers(10) ORDER BY number LIMIT 2 AFTER number IN (2, 3, 6) ALL;
```

```response theme={null}
┌─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`:

```sql theme={null}
SELECT number FROM numbers(10) ORDER BY number LIMIT 2 AFTER number IN (2, 6) ALL UNTIL number = 7;
```

```response theme={null}
┌─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:

```sql theme={null}
SELECT number FROM numbers(10) ORDER BY number LIMIT AFTER number IN (2, 6) ALL UNTIL number = 4;
```

```response theme={null}
┌─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`.
</Note>

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

```sql theme={null}
SELECT number FROM numbers(10) ORDER BY number LIMIT UNTIL number >= 3;
```

```response theme={null}
┌─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:

```sql theme={null}
SELECT number FROM numbers(10) ORDER BY number LIMIT 2 UNTIL number >= 3;
```

```response theme={null}
┌─number─┐
│      0 │
│      1 │
└────────┘
```

A range can follow [`LIMIT BY`](/reference/statements/select/limit-by) and then applies to the rows that `LIMIT BY` keeps:

```sql theme={null}
SELECT number % 4 AS k, number FROM numbers(12) ORDER BY k, number LIMIT 2 BY k LIMIT 3 AFTER k >= 1;
```

```response theme={null}
┌─k─┬─number─┐
│ 1 │      1 │
│ 1 │      5 │
│ 2 │      2 │
└───┴────────┘
```

<h2 id="considerations">
  Considerations
</h2>

**Non-deterministic results:** Without an [`ORDER BY`](/reference/statements/select/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](/reference/settings/session-settings/other#limit) setting.

<h2 id="see-also">
  See also
</h2>

* [LIMIT BY](/reference/statements/select/limit-by) — Limits rows per group of values, useful for getting top N results within each category.