SQL DISTINCT ON

SELECT DISTINCT ON (key) keeps one row for each distinct value of key: the first one in ORDER BY order. Sort each group's rows so the one you want comes first, and you get the latest order per customer, or the largest line per order, as whole rows.

select distinct on (customer_id)
       customer_id,
       order_id,
       order_date,
       status
from orders
order by customer_id, order_date desc

16 customers have placed an order, so that returns 16 rows. Customer 1 has three orders, and the one kept is order 19, the latest.

When to use it

Use it for "one row per group, and I want the rest of that row": the latest status per subscription, the first page of each session, the most expensive product in each category. GROUP BY with max(order_date) finds the date but loses the order_id and status next to it.

Syntax

• The DISTINCT ON list is the group. It can hold several expressions: distinct on (customer_id, status) keeps the latest order of each status for each customer.
• The ORDER BY picks the winner. Start it with the DISTINCT ON expressions, then the column that decides which row comes first.
• Without an ORDER BY, the row kept is whichever DuckDB reads first. See why DISTINCT ON returns the wrong row.

Break ties on purpose

When two rows tie on the sort, DISTINCT ON still returns one, and which one isn't defined. Order 2 has two lines with a quantity of 1, so "the largest line" needs a tie-breaker. Adding order_item_id makes the answer the same on every run:

select distinct on (order_id)
       order_id,
       order_item_id,
       product_id,
       quantity
from order_items
order by order_id, quantity desc, order_item_id

That's 24 rows, one per order. The correlated subquery in Lesson 10.9 · Latest row per group keeps both tied lines instead.

The portable form

row_number() numbers the rows in each group in the same order, and keeping number 1 gives the same result. DuckDB filters it with QUALIFY; engines without QUALIFY wrap it in a subquery:

select customer_id, order_id, order_date, status
from (
  select customer_id,
         order_id,
         order_date,
         status,
         row_number() over (
           partition by customer_id
           order by order_date desc
         ) as rn
  from orders
) as ranked
where rn = 1
order by customer_id

This runs on PostgreSQL, MySQL 8, SQL Server, and SQLite. It's longer, and it makes the same choice explicit: the window's ORDER BY is the tie-breaker.

What can go wrong

• No tie-breaker. Two rows that sort equally can swap between runs, and the report changes with them.
• The final sort is the picking sort. The result comes back in ORDER BY order, so to show the latest orders newest first, sort again outside: wrap the query and order the outer one.
• DuckDB is more lenient than PostgreSQL. DuckDB accepts order by order_date desc without the key first. PostgreSQL refuses with SELECT DISTINCT ON expressions must match initial ORDER BY expressions, so put the key first and the query runs on both.

How other databases differ