> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-mintlify-8a08bda2.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.
> Documentation for LIMIT BY Clause
# LIMIT BY Clause
A query with the `LIMIT n BY expressions` clause selects the first `n` rows for each distinct value of `expressions`. The key for `LIMIT BY` can contain any number of [expressions](/reference/syntax#expressions).
ClickHouse supports the following syntax variants:
* `LIMIT [offset_value, ]n BY expressions`
* `LIMIT n OFFSET offset_value BY expressions`
During query processing, ClickHouse selects data ordered by sorting key. The sorting key is set explicitly using an [ORDER BY](/reference/statements/select/order-by) clause or implicitly as a property of the table engine (row order is only guaranteed when using [ORDER BY](/reference/statements/select/order-by), otherwise the row blocks will not be ordered due to multi-threading). Then ClickHouse applies `LIMIT n BY expressions` and returns the first `n` rows for each distinct combination of `expressions`. If `OFFSET` is specified, then for each data block that belongs to a distinct combination of `expressions`, ClickHouse skips `offset_value` number of rows from the beginning of the block and returns a maximum of `n` rows as a result. If `offset_value` is bigger than the number of rows in the data block, ClickHouse returns zero rows from the block.
`LIMIT BY` is not related to [LIMIT](/reference/statements/select/limit). They can both be used in the same query.
If you want to use column numbers instead of column names in the `LIMIT BY` clause, enable the setting [enable\_positional\_arguments](/reference/settings/session-settings#enable_positional_arguments).
Examples
Sample table:
```sql theme={null}
CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);
```
Queries:
```sql theme={null}
SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id;
```
```text theme={null}
┌─id─┬─val─┐
│ 1 │ 10 │
│ 1 │ 11 │
│ 2 │ 20 │
│ 2 │ 21 │
└────┴─────┘
```
```sql theme={null}
SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id;
```
```text theme={null}
┌─id─┬─val─┐
│ 1 │ 11 │
│ 1 │ 12 │
│ 2 │ 21 │
└────┴─────┘
```
The `SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id` query returns the same result.
The following query returns the top 5 referrers for each `domain, device_type` pair with a maximum of 100 rows in total (`LIMIT n BY + LIMIT`).
```sql theme={null}
SELECT
domainWithoutWWW(URL) AS domain,
domainWithoutWWW(REFERRER_URL) AS referrer,
device_type,
count() cnt
FROM hits
GROUP BY domain, referrer, device_type
ORDER BY cnt DESC
LIMIT 5 BY domain, device_type
LIMIT 100;
```
`LIMIT BY` also works with negative limits and offsets. Similar to the [negative LIMIT clause](/reference/statements/select/limit#negative-limits), you can use negative values with `LIMIT BY` to select rows from the *end* of each group.
```sql theme={null}
SELECT * FROM limit_by ORDER BY id, val LIMIT -2 BY id;
```
```text theme={null}
┌─id─┬─val─┐
│ 1 │ 11 │
│ 1 │ 12 │
│ 2 │ 20 │
│ 2 │ 21 │
└────┴─────┘
```
Returns the last 2 rows for each `id`. For `id = 1` we get rows `11` and `12`; for `id = 2` both rows are returned because the group has only 2 rows.
```sql theme={null}
SELECT * FROM limit_by ORDER BY id, val LIMIT -1 OFFSET -1 BY id;
```
```text theme={null}
┌─id─┬─val─┐
│ 1 │ 11 │
│ 2 │ 20 │
└────┴─────┘
```
Returns the second-to-last row of each `id`: the trailing `OFFSET -1` drops the last row per group, and the leading `-1` then keeps the last row of what remains.
Different sign `LIMIT` and `OFFSET` can be mixed as well. For example, to drop each group's first row and then keep the last 2 of what remains:
```sql theme={null}
SELECT * FROM limit_by ORDER BY id, val LIMIT -2 OFFSET 1 BY id;
```
```text theme={null}
┌─id─┬─val─┐
│ 1 │ 11 │
│ 1 │ 12 │
│ 2 │ 21 │
└────┴─────┘
```
For `id = 1`, the first row (`10`) is skipped; the last 2 of `11, 12` are both returned. For `id = 2`, the first row (`20`) is skipped, leaving only `21`.
LIMIT BY ALL
`LIMIT BY ALL` is equivalent to listing all the SELECT-ed expressions that are not aggregate functions.
For example:
```sql theme={null}
SELECT col1, col2, col3 FROM table LIMIT 2 BY ALL;
```
is the same as
```sql theme={null}
SELECT col1, col2, col3 FROM table LIMIT 2 BY col1, col2, col3;
```
For a special case that if there is a function having both aggregate functions and other fields as its arguments, the `LIMIT BY` keys will contain the maximum non-aggregate fields we can extract from it.
For example:
```sql theme={null}
SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY ALL;
```
is the same as
```sql theme={null}
SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY substring(a, 4, 2), substring(a, 1, 2);
```
Examples
Sample table:
```sql theme={null}
CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);
```
Queries:
```sql theme={null}
SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id;
```
```text theme={null}
┌─id─┬─val─┐
│ 1 │ 10 │
│ 1 │ 11 │
│ 2 │ 20 │
│ 2 │ 21 │
└────┴─────┘
```
```sql theme={null}
SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id;
```
```text theme={null}
┌─id─┬─val─┐
│ 1 │ 11 │
│ 1 │ 12 │
│ 2 │ 21 │
└────┴─────┘
```
The `SELECT * FROM limit_by ORDER BY id, val LIMIT 2 OFFSET 1 BY id` query returns the same result.
Using `LIMIT BY ALL`:
```sql theme={null}
SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY ALL;
```
This is equivalent to:
```sql theme={null}
SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY id, val;
```