SQL Latest Row per Group

"Each customer's most recent order" sounds like a GROUP BY question, and max(order_date) does find each customer's latest date. It can't tell you which order that was. Add order_id to the select list and DuckDB stops with column "order_id" must appear in the GROUP BY clause: a group holds several orders, and the query hasn't said which one's id to show.

Why max(order_id) isn't the answer

The tempting fix is to take the highest id as well, on the theory that the newest order has the biggest number:

select customer_id, max(order_id) as highest_id, max(order_date) as latest_date
from orders
where customer_id in (7, 10, 13)
group by customer_id
order by customer_id

For these three customers the highest id is the wrong order. Customer 7's latest order, on April 7, is order 4; order 16 was placed in February. Each max() runs on its own column, so the two maximums can come from different rows. An id records when a row was inserted, not when the order happened; the MAX(id) as latest mistake page shows the same trap from the other side.

Keep the row that matches its group's maximum

Lesson 10.4 compared each product with its own category's average. The same move picks whole rows: compare each order's date with its own customer's latest date, and keep the orders that match.

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

For each outer order o, the subquery looks only at that customer's orders, through o2.customer_id = o.customer_id, and returns their latest date. An order survives when it's the one on that date. Because the outer query reads orders directly, with no GROUP BY, every column of the winning row is available: the id, the status, the shipped date. Swap max for min and you get each customer's first order instead.

Ties keep every winner

The comparison is =, so when two rows share a group's maximum, both are kept. No Garden Shop customer placed two orders on one day, but order lines tie often. Here is the largest line, by quantity, in each order:

select oi.order_id, p.product_name, oi.quantity
from order_items as oi
join products as p on p.product_id = oi.product_id
where oi.quantity = (
  select max(i2.quantity)
  from order_items as i2
  where i2.order_id = oi.order_id
)
order by oi.order_id, p.product_name

That's 28 rows for 24 orders. In orders 2, 9, 16, and 23 both lines have a quantity of 1, so both are "the largest". Sometimes that's the honest answer. When you need exactly one row per group, you need a tie-breaker, and the correlated form has no clean place for one. That's the job of row_number() with an ORDER BY that names one, filtered with QUALIFY in Lesson 11.6.

DuckDB's shortcut

DuckDB has an aggregate for exactly this question. arg_max(order_id, order_date) returns the order_id from the row with the latest order_date, inside an ordinary GROUP BY:

select customer_id, arg_max(order_id, order_date) as latest_order_id
from orders
group by customer_id
order by customer_id

It returns one value per group even when there's a tie, and which tied row it picks isn't defined. DuckDB also spells it max_by, the name Spark, Trino, and Snowflake use. PostgreSQL, MySQL, and SQL Server have no equivalent. The correlated subquery runs on all of them, which is why it's still the form to know. To keep the whole row rather than one value, DuckDB and PostgreSQL also have DISTINCT ON.