Menu

PostgreSQL DISTINCT ON: One Row per Group

SELECT DISTINCT ON (key) ... ORDER BY key, sort keeps the first row of each group, such as each customer's latest order. It is PostgreSQL-only.

This page includes runnable editors. Edit, run, and see output instantly.

SELECT DISTINCT ON (expr) ... returns one row for each distinct value of expr: the first row of that group in ORDER BY order. Sort by the group key first, then by what decides "first". For each customer's latest order, that is ORDER BY customer, created_at DESC.

It returns three rows: order 2 for asha, 4 for ben and 5 for chen, each customer's most recent. DISTINCT ON is a PostgreSQL extension. Other databases need the window-function form shown below.

How the ORDER BY decides which row is kept

PostgreSQL sorts the rows, then keeps the first row each time the DISTINCT ON value changes. So:

  • The ORDER BY must begin with the DISTINCT ON expressions, or PostgreSQL raises "SELECT DISTINCT ON expressions must match initial ORDER BY expressions".
  • Without an ORDER BY past those expressions, which row is kept is unpredictable and can change between runs.

Change created_at DESC to total DESC and the same query returns each customer's largest order:

Now asha's row is order 1 (40) and ben's is order 3 (60).

Break ties on purpose

When two rows tie on the sort column, either can win. Add a last sort key that is unique, such as the primary key, so the result is the same every time:

Rows 2 and 3 both have 95 points. id as the final key makes row 2 the winner.

Group by more than one column

List several expressions to get one row per combination:

Each sensor and kind pair keeps its latest reading: s1 humidity 40, s1 temp 22, s2 temp 19.

The portable version with ROW_NUMBER

ROW_NUMBER() numbers the rows inside each partition. Keeping number 1 gives the same result as DISTINCT ON, and it works in MySQL 8, SQL Server and SQLite too:

SELECT customer, id, total, created_at
FROM (
    SELECT o.*,
           ROW_NUMBER() OVER (PARTITION BY customer ORDER BY created_at DESC) AS rn
    FROM orders o
) ranked
WHERE rn = 1;

Use this form when you need the top n per group (rn <= 3) or when the query has to run on another database. The window functions page explains PARTITION BY.

Make it fast

DISTINCT ON sorts the input, so on a large table give it an index that matches the ORDER BY:

CREATE INDEX orders_customer_latest ON orders (customer, created_at DESC);

PostgreSQL can then read the rows in order instead of sorting them. With few groups and many rows per group, a LATERAL join that takes LIMIT 1 per customer from the same index usually reads fewer rows. The joins page shows LATERAL. Compare both with EXPLAIN ANALYZE on your data.

Frequently Asked Questions

What is the difference between DISTINCT and DISTINCT ON?

DISTINCT removes rows that are identical in every selected column. DISTINCT ON (a) keeps one row for each value of a, and the other columns come from whichever row sorts first in that group.

Is DISTINCT ON standard SQL?

No. It is a PostgreSQL extension, so it does not work in MySQL or SQL Server. The portable version is ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) in a subquery, filtered to row number 1.

Why do I get "SELECT DISTINCT ON expressions must match initial ORDER BY expressions"?

The ORDER BY must start with the same expressions as DISTINCT ON, in the same order. Write DISTINCT ON (customer) ... ORDER BY customer, created_at DESC, not ORDER BY created_at DESC alone.

Can DISTINCT ON return the top 3 rows per group?

No, it returns exactly one row per group. For the top n, number the rows with ROW_NUMBER() and keep rn <= 3, or use a LATERAL subquery with LIMIT 3.

Coddy programming languages illustration

Learn to code with Coddy

Get started