> ## Documentation Index
> Fetch the complete documentation index at: https://private-7c7dfe99-fix-nav-issues.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

> Documentation for DISTINCT Clause

# DISTINCT Clause

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`.
* When [ORDER BY](/reference/statements/select/order-by) is omitted and [LIMIT](/reference/statements/select/limit) is defined, the query stops running immediately after the required number of different rows has been read.
* Data blocks are output as they are processed, without waiting for the entire query to finish running.