> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-parallel-read-in-order-multi-part.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for DISTINCT Clause

# DISTINCT

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:

```text theme={null}
┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘
```

Using `DISTINCT` without specifying columns:

```sql theme={null}
SELECT DISTINCT * FROM t1;
```

```text theme={null}
┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 1 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘
```

Using `DISTINCT` with specified columns:

```sql theme={null}
SELECT DISTINCT ON (a,b) * FROM t1;
```

```text theme={null}
┌─a─┬─b─┬─c─┐
│ 1 │ 1 │ 1 │
│ 2 │ 2 │ 2 │
│ 1 │ 2 │ 2 │
└───┴───┴───┘
```

<h2 id="distinct-and-order-by">
  DISTINCT and ORDER BY
</h2>

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:

```text theme={null}
┌─a─┬─b─┐
│ 2 │ 1 │
│ 1 │ 2 │
│ 3 │ 3 │
│ 2 │ 4 │
└───┴───┘
```

Selecting data:

```sql theme={null}
SELECT DISTINCT a FROM t1 ORDER BY b ASC;
```

```text theme={null}
┌─a─┐
│ 2 │
│ 1 │
│ 3 │
└───┘
```

Selecting data with the different sorting direction:

```sql theme={null}
SELECT DISTINCT a FROM t1 ORDER BY b DESC;
```

```text theme={null}
┌─a─┐
│ 3 │
│ 1 │
│ 2 │
└───┘
```

Row `2, 4` was cut before sorting.

Take this implementation specificity into account when programming queries.

<h2 id="null-processing">
  Null Processing
</h2>

`DISTINCT` works with [NULL](/reference/syntax#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.

<h2 id="alternatives">
  Alternatives
</h2>

It is possible to obtain the same result by applying [GROUP BY](/reference/statements/select/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](/reference/statements/select/order-by) can stop as soon as it has read enough different rows to satisfy [LIMIT](/reference/statements/select/limit).
* Before external execution starts and when `ORDER BY` is omitted, a [`LIMIT ... AFTER ... UNTIL`](/reference/statements/select/limit#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.

<h2 id="distinct-in-external-memory">
  DISTINCT in External Memory
</h2>

`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:

```sql theme={null}
SELECT DISTINCT number % 1000000 AS id
FROM numbers(2000000)
SETTINGS
    max_bytes_before_external_distinct = 16777216,
    max_bytes_ratio_before_external_distinct = 0,
    max_memory_usage = 268435456;
```

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.