Skip to content

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.