Keeping the Latest Row Per Key
The 'most recent record per customer' pattern with partition by key, order by date.
The Most-Recent-Per-Key Question
"Return the most recent order for each customer." "Get the latest status for every device." This latest-row-per-key problem is one of the highest-frequency SQL interview tasks because it appears constantly in real analytics work.
It is a specialized top-1-per-group: partition by the key, order by the timestamp descending, and keep the first row. This lesson drills the pattern and its alternatives.
Why MAX Alone Falls Short
A tempting first answer is MAX(order_date) grouped by customer. That gives the latest date, but not the rest of that order's row, the order id, amount, or status.
If the interviewer wants the full latest row, MAX with GROUP BY needs an extra join back to the table on the key and the max date, which is verbose and can break on ties. Window functions are cleaner.
-- Gives the date, not the full row
SELECT customer_id, MAX(order_date) AS last_order
FROM orders
GROUP BY customer_id;All lessons in this course
- Top-N Rows Per Group With ROW_NUMBER
- Handling Ties in Top-N
- Deduplicating Rows Safely
- Keeping the Latest Row Per Key