If SELECT DISTINCT is specified, only unique rows will remain in a query result. Thus, only a single row will remain out of all the sets of fully matching rows in the result.
You can specify the list of columns that must have unique values: SELECT DISTINCT ON (column1, column2,...). If the columns are not specified, all of them are taken into consideration.
Consider the table:
Using DISTINCT without specifying columns:
Using DISTINCT with specified columns:
DISTINCT and ORDER BY
ClickHouse supports using the DISTINCT and ORDER BY clauses for different columns in one query. The DISTINCT clause is executed before the ORDER BY clause.
Consider the table:
Selecting data:
Selecting data with the different sorting direction:
Row 2, 4 was cut before sorting.
Take this implementation specificity into account when programming queries.
Null Processing
DISTINCT works with NULL as if NULL were a specific value, and NULL==NULL. In other words, in the DISTINCT results, different combinations with NULL occur only once. It differs from NULL processing in most other contexts.
Alternatives
It is possible to obtain the same result by applying GROUP BY across the same set of values as specified as SELECT clause, without using any aggregate functions. But there are few differences from GROUP BY approach:
DISTINCT can be applied together with GROUP BY.- Before external execution starts, a query without ORDER BY can stop as soon as it has read enough different rows to satisfy LIMIT.
- Before external execution starts and when
ORDER BY is omitted, a LIMIT ... AFTER ... UNTIL range without ALL can also stop the query once the range has ended.
- Data blocks are output as they are processed until external execution starts.
DISTINCT in External Memory
DISTINCT can write temporary data to disk to process sets of unique values that are too large to
keep in memory. This requires additional disk I/O and can make queries slower.
Two settings control when spilling starts:
max_bytes_before_external_distinct sets a threshold in bytes of total query memory. It defaults
to 0 (disabled).max_bytes_ratio_before_external_distinct sets a fraction of available memory under server or
user limits, measured at the start of execution. It defaults to 0.5 and has no effect when
neither limit applies.
When both thresholds apply, the smaller is used. Set both settings to 0 to disable spilling.
max_memory_usage does not affect the ratio. To configure spilling relative to a query memory limit,
set an absolute threshold below that limit. For example, this query uses a 16 MiB spill threshold
with a 256 MiB query memory limit:
These thresholds do not cap memory usage. Leave room for other query processing and the spill
itself. Spilling may also start earlier under memory pressure.
Rows can be returned before spilling, and a LIMIT satisfied at this stage can finish the query early.
Once spilling starts, the rest of the input must be read before the remaining results can be returned.
If the query includes ORDER BY, those results are returned in the requested order.
When DISTINCT uses input sorted by a prefix of its keys, it does not spill. A large group of rows
with the same prefix can still use substantial memory.
As with optimize_distinct_in_order, spilling may deduplicate floating-point values that have
different binary representations but compare equal, including 0.0 and -0.0, or NaN values
with different payloads. Last modified on September 18, 2026
