LIMIT clause

[Edit this page](https://github.com/ClickHouse/ClickHouse/tree/master/docs/en/sql-reference/statements/select/limit.md)

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

## Basic syntax[​](#basic-syntax "Direct link to 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[​](#negative-limits "Direct link to 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[​](#fractional-limits "Direct link to 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%) | | | | | | | |

Note

- Fractions must be [Float64](/docs/sql-reference/data-types/float) values greater than 0 and less than 1.
- Fractional row counts are rounded to the next whole number.

## Combining limit types[​](#combining-limit-types "Direct link to 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[​](#limit--with-ties-modifier "Direct link to 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.

Note

`WITH TIES` is not supported with negative limits.

This modifier can be combined with the [`ORDER BY ... WITH FILL`](/docs/sql-reference/statements/select/order-by#order-by-expr-with-fill-modifier) modifier.

## Considerations[​](#considerations "Direct link to Considerations")

**Non-deterministic results:** Without an [`ORDER BY`](/docs/sql-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](/docs/operations/settings/settings#limit) setting.

## See also[​](#see-also "Direct link to See also")

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

- [Basic syntax](#basic-syntax)- [Negative limits](#negative-limits)- [Fractional limits](#fractional-limits)- [Combining limit types](#combining-limit-types)- [LIMIT ... WITH TIES](#limit--with-ties-modifier)- [Considerations](#considerations)- [See also](#see-also)

Was this page helpful?