How do you return each customer's most recent order row, with all of its columns?

## The requirement "The latest row per key" is the most frequently written query in this whole family: the newest order per customer, the current status per ticket, the last reading per sensor, the most recent price per product. It is top-N-per-group with N fixed at 1, and the output must be the entire row — not just the maximum timestamp, but the order id, amount and status that belong to it. ## The query ```sql WITH ranked AS ( SELECT o.*, ROW_NUMBER() OVER (PARTITION BY o.customer_id ORDER BY o.order_date DESC, o.order_id DESC) AS rn FROM orders o ) SELECT customer_id, order_id, order_date, status, total_amount FROM ranked WHERE rn = 1; ``` Read it in three beats. `PARTITION BY customer_id` restarts the numbering for every customer. `ORDER BY order_date DESC` puts the newest order first, so it receives number 1. The outer `WHERE rn = 1` keeps exactly that row. The filter has to sit in an outer level because a window function cannot appear in `WHERE` — at the time `WHERE` is evaluated the rank has not been computed yet. ## Why the aggregate version is not enough The instinctive first attempt is `SELECT customer_id, MAX(order_date) FROM orders GROUP BY customer_id`. That correctly finds *when* each customer last ordered, but it throws away the row: you cannot add `order_id` or `status` to the select list, because grouping has collapsed many rows into one and those columns have no single value. Getting the full row back from an aggregate requires joining the aggregate result back to `orders` on both `customer_id` and `order_date` — an extra join that also duplicates any customer with two orders at the identical timestamp. The window-function version keeps the row intact from the start, which is why it has become the default idiom. ## Ties and determinism Suppose a customer placed two orders recorded with the exact same `order_date`. With `ROW_NUMBER()` one of them gets 1 and the other gets 2, so the query still returns a single row per customer — but *which* of the two is unspecified, and a second run may pick the other one. That instability is invisible in small test data and surfaces later as a report that changes without any data changing. The fix is to make the ordering total by appending a column that is unique within the partition, typically the primary key: `ORDER BY order_date DESC, order_id DESC`. Now the newest order is defined precisely and the query is reproducible. If, instead, you genuinely want both tied orders returned, use `RANK() OVER (...) = 1`, which assigns 1 to every row sharing the top value. Choosing `RANK()` by accident is the classic cause of "my per-customer query returns more rows than I have customers". ## Timestamps, dates and the ordering column The pattern is only as good as the ordering column. If `order_date` is a date rather than a timestamp, every order placed on the same day ties, and the tiebreaker does all the work. If the column is nullable, engines differ on default NULL placement in `DESC` order, so an order with a NULL date can be picked as "most recent"; write `NULLS LAST` explicitly or filter the NULLs out in the inner query. ## What the query does not return Because the pattern reads `orders`, a customer who has never ordered produces no partition and therefore no output row. If the report must list every customer, start from `customers` and outer-join to the ranked set instead — the ranking logic is unchanged, only the driving table differs. ## Variations you should be able to write on the spot Changing `rn = 1` to `rn <= 5` gives the five most recent orders per customer with no other edit. Reversing to `ORDER BY order_date ASC` gives each customer's first order — useful for cohort and acquisition analysis. Partitioning by two columns (`PARTITION BY customer_id, product_id`) gives the latest order of each product per customer. The shape never changes; only the `OVER` clause and the outer predicate do.