LIMIT BY Clause

    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 , 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 . They can both be used in the same query.

    Sample table:

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

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

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