DISTINCT and DISTINCT ON

Removes duplicate result rows; DISTINCT ON keeps the first row of each group.

Syntax

SELECT DISTINCT col1, col2 FROM ...;
SELECT DISTINCT ON (key_expr) col1, col2 FROM ... ORDER BY key_expr, sort_expr;
COUNT(DISTINCT expr)

SELECT DISTINCT removes rows that are identical in every selected column. It applies to the whole row, not to the first column.

Two NULLs count as duplicates of each other for DISTINCT (unlike =).
COUNT(DISTINCT x) counts different non-NULL values of x.
DISTINCT ON (expr) (PostgreSQL extension) keeps the first row for each distinct value of expr, where "first" is defined by ORDER BY. The ORDER BY must start with the DISTINCT ON expressions. It is the shortest way to get "latest row per key".
Reaching for DISTINCT to hide duplicates produced by a join usually hides a bug (a fan-out). Find out why the rows repeat first.

Examples

Distinct values starter

SELECT DISTINCT city
FROM customers
ORDER BY city;

One row per city that appears at least once.

Distinct combinations and counts starter

SELECT count(*) AS orders,
       count(DISTINCT customer_id) AS customers,
       count(DISTINCT product_id) AS products
FROM orders;

COUNT(DISTINCT ...) counts different values; count(*) counts rows.

Latest order per customer with DISTINCT ON shop

SELECT DISTINCT ON (customer_id)
       customer_id, order_id, ordered_at, status
FROM orders
ORDER BY customer_id, ordered_at DESC, order_id DESC
LIMIT 10;

For each customer, the row that sorts first — the most recent order.

Pitfalls

Thinking DISTINCT applies to one column: SELECT DISTINCT city, name returns distinct (city, name) pairs.
Using DISTINCT to patch duplicate rows caused by a join instead of fixing the join or pre-aggregating.
DISTINCT ON without a matching ORDER BY: which row survives is then arbitrary.
With SELECT DISTINCT, ORDER BY may only use selected expressions.

Other databases

For orientation only: SQL Quest executes PostgreSQL.

MySQL: No DISTINCT ON; use ROW_NUMBER() in a subquery.
SQL Server: No DISTINCT ON; use ROW_NUMBER() in a subquery or CTE.
BigQuery: No DISTINCT ON; use QUALIFY ROW_NUMBER() OVER (...) = 1.
Snowflake: No DISTINCT ON; use QUALIFY ROW_NUMBER() OVER (...) = 1.