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

Note

LIMIT BY is not related to LIMIT. They can both be used in the same query.

Examples

Sample table:

  1. CREATE TABLE limit_by(id Int, val Int) ENGINE = Memory;
  2. INSERT INTO limit_by VALUES (1, 10), (1, 11), (1, 12), (2, 20), (2, 21);

Queries:

  1. SELECT * FROM limit_by ORDER BY id, val LIMIT 2 BY id
  1. ┌─id─┬─val─┐
  2. 1 10
  3. 1 11
  4. 2 20
  5. 2 21
  6. └────┴─────┘
  1. SELECT * FROM limit_by ORDER BY id, val LIMIT 1, 2 BY id
  1. ┌─id─┬─val─┐
  2. 1 11
  3. 1 12
  4. 2 21
  5. └────┴─────┘

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

  1. SELECT
  2. domainWithoutWWW(URL) AS domain,
  3. domainWithoutWWW(REFERRER_URL) AS referrer,
  4. device_type,
  5. count() cnt
  6. FROM hits
  7. GROUP BY domain, referrer, device_type
  8. ORDER BY cnt DESC
  9. LIMIT 5 BY domain, device_type
  10. LIMIT 100