DISTINCT ON — PostgreSQL-specific, when to use it.

DISTINCT ON (expr) is a PostgreSQL extension that keeps the first row for each distinct value of expr, as determined by ORDER BY — the most concise way to write "the latest/best row per group." Standard DISTINCT dedups whole rows; DISTINCT ON dedups by a chosen key and lets you pick which row survives.

The canonical use: latest row per group

"Most recent order per customer":

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

For each customer_id, PostgreSQL keeps the first row after sorting — and because we sort created_at DESC within each customer, "first" means "newest." Clean, single-pass, no subquery.

The iron rule: ORDER BY must lead with the DISTINCT ON keys

The leftmost ORDER BY columns must match the DISTINCT ON expressions, then you add the tiebreaker that decides which row wins:

DISTINCT ON (customer_id)            -- the group key
ORDER BY customer_id, created_at DESC -- key first, then "which row"

Omit or reorder this and PostgreSQL either errors or returns an arbitrary row per group.

Versus the alternatives

-- ROW_NUMBER equivalent (more portable, more verbose)
SELECT * FROM (
  SELECT *, ROW_NUMBER() OVER (
    PARTITION BY customer_id ORDER BY created_at DESC) AS rn
  FROM orders
) t WHERE rn = 1;

A second common use is deduplication: SELECT DISTINCT ON (email) * FROM users ORDER BY email, id keeps one canonical row per email.