Window functions · Medium
How do you get each customer's most recent order (or de-duplicate rows)?
Short answer
Number each customer's orders with ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC, order_id DESC) and keep the rows numbered 1. The partition defines the group, the ORDER BY defines which row wins, and the extra tie-breaker makes the result deterministic. The same pattern removes duplicate records while keeping the newest copy.
Detailed answer
WITH ranked AS (
SELECT o.*,
ROW_NUMBER() OVER (
PARTITION BY customer_id
ORDER BY order_date DESC, order_id DESC
) AS rn
FROM orders o
)
SELECT r.customer_id, c.full_name, r.order_id, r.order_date, r.status
FROM ranked r
JOIN customers c ON c.customer_id = r.customer_id
WHERE r.rn = 1
ORDER BY r.order_date DESC;
Alternative without window functions — join to the max date per customer:
SELECT o.customer_id, o.order_id, o.order_date
FROM orders o
JOIN (
SELECT customer_id, MAX(order_date) AS last_date
FROM orders
GROUP BY customer_id
) m ON m.customer_id = o.customer_id AND m.last_date = o.order_date
ORDER BY o.order_date DESC;
The second version returns two rows for a customer who placed two orders on their latest date. Pointing out that difference — and choosing ROW_NUMBER with a tie-breaker when exactly one row is required — is what separates a strong answer.