Skip to content
Tutorials Center
My learning

Topics

Lesson 14 of 35 ยท Querying and Functions

SELECT in ClickHouse: Extras

Go beyond standard SELECT with ClickHouse features: LIMIT BY, WITH FILL, PREWHERE, SAMPLE, WITH TOTALS, ARRAY JOIN, aliases, CTEs, and the FORMAT clause.

ClickHouse SQL covers what you already know (WHERE, GROUP BY, HAVING, ORDER BY, LIMIT, subqueries, CTEs) and adds clauses designed for analytics: top-N per group, filling gaps in a time series, totals rows, sampling, and early filtering. This lesson tours them. Each ClickHouse example is read-only, with an SQLite stand-in that produces the same result by standard means so you can see what the clause does.

ClickHouse cannot run on this site. The stand-ins use window functions, recursive CTEs and joins, which are portable SQL. They are longer than the ClickHouse versions, which is the point of the extras.

LIMIT BY: top N per group

LIMIT n BY expr keeps at most n rows per distinct value of expr, after ORDER BY. It is the shortest way to get "top 2 pages per country".

SQL
SELECT country, page, count() AS views
FROM page_views
GROUP BY country, page
ORDER BY country, views DESC
LIMIT 2 BY country;

The standard-SQL way needs a window function:

SQL Stand-in: top 2 pages per country (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

LIMIT n OFFSET m BY expr skips m rows per group first. Note that plain LIMIT 10 after LIMIT 2 BY ... still applies to the whole result.

WITH FILL: no missing days

A GROUP BY day leaves out days with no rows, which breaks charts. ORDER BY ... WITH FILL inserts the missing values:

SQL
SELECT toDate(event_time) AS day, count() AS views
FROM page_views
GROUP BY day
ORDER BY day WITH FILL
    FROM toDate('2026-05-01') TO toDate('2026-05-08') STEP 1;

Added days get 0 (the default value) for the other columns. STEP INTERVAL 1 HOUR works for time, and INTERPOLATE (views AS views) can carry the previous value forward instead of zero. In standard SQL you build a calendar and LEFT JOIN it:

SQL Stand-in: fill missing days with a calendar (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

WHERE and PREWHERE

PREWHERE is a ClickHouse optimisation: it filters on a (usually small) column first, and only then reads the other columns for the rows that passed. It is automatic when optimize_move_to_prewhere = 1 (the default), so you rarely write it. Write it manually only for a very selective filter on a narrow column:

SQL
SELECT url, ms
FROM page_views
PREWHERE country = 'PT'          -- read the small country column first
WHERE ms > 1000;

SAMPLE: approximate queries

If the table is created with a SAMPLE BY expression (a hash in the key), you can read a fraction of the data:

SQL
-- table: ... ORDER BY (site, intHash32(user_id)) SAMPLE BY intHash32(user_id)
SELECT count() * 10 AS estimated_views
FROM page_views SAMPLE 0.1;      -- read about 10 percent

Multiply additive results by the inverse of the fraction (or use _sample_factor). It trades exactness for speed on huge tables and is great for exploratory work.

WITH TOTALS, ROLLUP and CUBE

WITH TOTALS adds a final row aggregating all groups; WITH ROLLUP and WITH CUBE add subtotal rows:

SQL
SELECT country, page, count() AS views
FROM page_views
GROUP BY country, page WITH ROLLUP;

Rollup rows have the grouped columns set to their default (empty string). Standard SQL has GROUP BY ROLLUP, which SQLite lacks; a UNION ALL of the group levels does the same:

SQL Stand-in: subtotals with UNION ALL (SQLite)

Press Run (or Ctrl + Enter) to see the result here.

ARRAY JOIN

ARRAY JOIN turns an array column into rows, one per element, repeating the other columns. It is the ClickHouse way to "unnest" tags or event lists (details in arrays and lambdas).

SQL
SELECT post_id, tag
FROM posts
ARRAY JOIN tags AS tag;

Aliases, CTEs and expression reuse

Aliases can be used anywhere in the same query, and WITH supports both CTEs and expression aliases:

SQL
WITH 7 AS days_back,
     toStartOfDay(now()) AS today
SELECT count()
FROM page_views
WHERE event_time >= today - INTERVAL days_back DAY;
SQL
WITH recent AS (SELECT * FROM page_views WHERE event_time > now() - INTERVAL 1 DAY)
SELECT country, count() FROM recent GROUP BY country;

Limits, DISTINCT and ordering details

  • SELECT DISTINCT ON (...) is not supported; use LIMIT 1 BY col instead.
  • ORDER BY ... COLLATE 'tr' sorts by language rules; ORDER BY ... NULLS LAST controls nulls.
  • LIMIT n WITH TIES includes rows that tie with the last row.
  • ORDER BY on a huge result needs memory: pair it with LIMIT, or the sort spills to disk if configured.

The FORMAT clause and SETTINGS

Every SELECT can end with FORMAT name (see clients, HTTP and formats) and a SETTINGS clause applying to that query only:

SQL
SELECT * FROM system.numbers LIMIT 5
SETTINGS max_threads = 1
FORMAT Pretty;

system.numbers and numbers(10) are handy infinite or small generators for quick experiments:

SQL
SELECT number, number * number AS square FROM numbers(5);

Common beginner mistakes

  • Using LIMIT 3 and expecting three rows per group (that is LIMIT 3 BY).
  • Forgetting that WITH FILL needs ORDER BY on the filled column.
  • Writing PREWHERE everywhere. The optimizer already does it.
  • Using SAMPLE on a table created without SAMPLE BY.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does LIMIT 2 BY country do?

    LIMIT n BY expr gives the top n rows per group.
  2. 2. What problem does ORDER BY ... WITH FILL solve?

    WITH FILL inserts the missing values (and default values for other columns).
  3. 3. What does PREWHERE do?

    It is an I/O optimisation that is usually applied automatically.
  4. 4. Which clause turns an array column into one row per element?

    ARRAY JOIN unnests arrays, repeating the other columns.

Summary

  • LIMIT n BY gives top-N per group; WITH FILL completes series.
  • PREWHERE is mostly automatic; SAMPLE reads a fraction for estimates.
  • WITH TOTALS, ROLLUP and CUBE add summary rows.
  • ARRAY JOIN unnests arrays; WITH defines CTEs and reusable expressions.
  • Every query can end with SETTINGS and FORMAT.

Next: Date, time and string functions