DISTINCT

What it does

SELECT DISTINCT collapses rows that are identical across every column in the select list into one.

SELECT DISTINCT status FROM orders;

Loading editor…

It's not per-column

DISTINCT is not a function you apply to one column — it's a modifier on the whole row. This returns every distinct pair, not distinct customers:

Loading editor…

If you want one row per customer, you need GROUP BY customer_id (and decide what to do with status), not DISTINCT.

COUNT(DISTINCT …)

Counting unique values of one column is a separate thing — it goes inside COUNT:

Loading editor…

DISTINCT ON (Postgres)

Postgres has DISTINCT ON (expr) — keep the first row per expr after ordering. It's a shortcut for the top-N-per-group pattern:

SELECT DISTINCT ON (customer_id) customer_id, order_id, amount
FROM orders
ORDER BY customer_id, order_date DESC;

Gotchas

• DISTINCT sorts or hashes the whole result to find duplicates — it's not free on large sets.
• It can mask a join bug. If a join fans out and you "fix" the row count with DISTINCT, you've hidden the real problem instead of solving it.
• NULLs are treated as equal here (unlike in WHERE) — two rows that are NULL in the same column count as duplicates.