Common mistake

SQL: Why MAX(id) Isn't the Latest Row

An id records when a row was inserted, not when the thing it describes happened. Order by the date to find the latest, with the id as a tie-breaker.

The symptom

You want each customer's most recent order, and the highest order_id looks like a shortcut: ids go up over time, so the biggest one must be the newest. For most Garden Shop customers it is. For three of them it isn't, and nothing in the result says which three.

Why it happens

An id says when a row was inserted. The order date says when the order was placed. They drift apart all the time: orders keyed in late, a backfill from an old system, a batch import that arrives out of order, a migration that renumbers rows. Customer 10's order 24 was placed on 2024-01-30, but their order 8 was placed on 2024-04-22. The higher id is the older order.

Some id schemes don't even try to be in order. UUIDs sort randomly, and databases that hand out ids in blocks can give a later row a smaller number.

The fix

Say what "latest" means: order by the date that records it, newest first, and keep the first row per customer. The id stays as a tie-breaker, for a customer who places two orders on the same day.

Wrong: highest order_id

select o.customer_id, o.order_id, o.order_date
from orders as o
where o.order_id = (
  select max(o2.order_id)
  from orders as o2
  where o2.customer_id = o.customer_id
)
order by o.customer_id

Right: latest order_date

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

Thirteen of the sixteen rows agree, which is why the shortcut survives review. Customers 7, 10, and 13 differ: the id-based query hands back orders from February, January, and April when each of them ordered again later.

A shorter form in DuckDB

arg_max(order_id, order_date) returns the order_id from the row with the latest order_date, in one aggregate: select customer_id, arg_max(order_id, order_date) from orders group by customer_id. It's DuckDB-specific (PostgreSQL and MySQL don't have it), and it picks one row arbitrarily when dates tie, so the row_number() form above is the portable one.