How do you write basic SELECT queries in ClickHouse with GROUP BY, ORDER BY, and LIMIT?

Think of ClickHouse SQL as a dialect of standard SQL that was fine-tuned for data analysts. Just as British English and American English are mutually intelligible but have different idioms and preferences, ClickHouse SQL follows the same grammar as PostgreSQL or MySQL but adds analytical idioms like count() without arguments, array functions, and approximate aggregation functions that make analytical queries more concise and performant.

ClickHouse supports the standard SQL query structure: SELECT columns, FROM tables, WHERE filters, GROUP BY dimensions, HAVING post-aggregation filters, ORDER BY sort expressions, and LIMIT row count. The query optimizer evaluates filters before aggregation (pushing WHERE predicates down), applies GROUP BY to create result groups, evaluates HAVING on aggregated results, sorts by ORDER BY, and finally applies LIMIT. This is the same logical execution order as any SQL database.

Internally, ClickHouse extends standard SQL with powerful analytical functions. The count() function without arguments counts rows (equivalent to COUNT(*) in other databases). The uniq() function provides approximate distinct counts using HyperLogLog (much faster than COUNT(DISTINCT) on large datasets). Quantile functions like quantile(0.99)(column) calculate percentiles efficiently. The arrayJoin() function unnests arrays inline. LowCardinality columns work like enums with dictionary encoding. The WITH TOTALS clause adds a summary row showing aggregates across all groups without a separate query.

At production scale, understanding query execution helps write performant queries. ClickHouse reads only the columns referenced in the query (columnar advantage). The WHERE clause leverages the primary key index to skip irrelevant granules. GROUP BY is executed using hash tables in memory, so queries grouping by high-cardinality columns (millions of distinct values) need sufficient RAM. The ORDER BY after GROUP BY sorts only the aggregated results (typically small), not the raw data. LIMIT is applied last and prevents unnecessary result serialization.

The non-obvious gotcha is that ClickHouse processes GROUP BY differently from row-oriented databases. In PostgreSQL, GROUP BY on a large table often triggers a disk-based sort, while ClickHouse uses in-memory hash tables that are extremely fast but can OOM on very high cardinality groupings. If your GROUP BY produces millions of groups, use LIMIT BY (ClickHouse-specific) to cap results per group, or set max_rows_to_group_by with an overflow mode to protect against memory exhaustion.