Common mistake

SQL: Why DISTINCT ON Returns the Wrong Row

DISTINCT ON keeps the first row of each group in ORDER BY order. Sort by the key alone and nothing decides which row is first, so you can get an old row instead of the latest.

The symptom

You want each Garden Shop customer's latest order, so you write select distinct on (customer_id) ... and sort by customer_id. You get one row per customer, 16 in all, and they look right. But customer 1's row is order 1 from 2024-03-02, while they ordered again on 2024-06-14 (order 19), and customers 3, 4, and 6 also come back with an older order.

Why it happens

DISTINCT ON keeps the first row of each group, and "first" means first in ORDER BY order. Sorting by customer_id alone puts each customer's rows together but says nothing about their order inside the group. Every one of customer 1's three orders is an equally good "first", so DuckDB keeps whichever it reached first. Here that was the oldest order for four of the seven repeat customers, and a different engine or a larger table can pick differently.

The fix

After the DISTINCT ON key, add the column that decides the winner, sorted so the winner comes first. For the latest order, that's order_date desc:

Wrong: sorted by the key only

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

Right: the latest date comes first

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

The fixed query keeps order 19 for customer 1, order 22 for customer 3, order 13 for customer 4, and order 23 for customer 6. Three of those four haven't shipped yet, which the wrong version hid.

Break ties as well

If two rows can share the deciding value, add one more column that can't tie, such as the id. No Garden Shop customer placed two orders on the same day, but order lines tie on quantity all the time:

A tie-breaker that can't tie

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

Dialect note

PostgreSQL also has DISTINCT ON, and the same rule: with only the key in ORDER BY, the row kept is unpredictable. Engines without it use row_number() and keep row 1, where a missing ORDER BY inside the window does the same damage: see why deduplicating without ORDER BY keeps the wrong row.