LIMIT BY Clause

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

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 clause or implicitly as a property of the table engine (row order is only guaranteed when using 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. 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.

Examples

Sample table:

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:

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id;

┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id;

┌─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).

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, you can use negative values with LIMIT BY to select rows from the end of each group.

SELECT * FROM limit_by ORDER BY id, val LIMIT -2 BY id;

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

SELECT * FROM limit_by ORDER BY id, val LIMIT -1 OFFSET -1 BY id;

┌─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:

SELECT * FROM limit_by ORDER BY id, val LIMIT -2 OFFSET 1 BY id;

┌─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:

SELECT col1, col2, col3 FROM table LIMIT 2 BY ALL;

is the same as

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:

SELECT substring(a, 4, 2), substring(substring(a, 1, 2), 1, count(b)) FROM t LIMIT 2 BY ALL;

is the same as

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:

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:

SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id;

┌─id─┬─val─┐
│  1 │  10 │
│  1 │  11 │
│  2 │  20 │
│  2 │  21 │
└────┴─────┘

SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id;

┌─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:

SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY ALL;

This is equivalent to:

SELECT id, val FROM limit_by ORDER BY id, val LIMIT 2 BY id, val;