Latest order for each customer
Keep only the most recent row per customer using a CTE and ROW_NUMBER.
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 customer_id, order_id, order_date, amount
FROM ranked
WHERE rn = 1
ORDER BY customer_id;How it works
The CTE numbers each customer's orders from the newest to the oldest. The main query keeps number 1, which is the latest order.
A CTE (WITH ... AS) is a named subquery. It makes the steps readable from top to bottom, and the same name can be used more than once in the main query.
What your tables need
- customers(customer_id, customer_name, region) - orders(order_id, customer_id, order_date, amount, status)
Rename the tables and columns to match your own database. Dates are stored as text in the form 2024-03-31 (or as a real date type, which works the same way here).
Small example
Input data
orders
order_id | customer_id | order_date | amount | status
1 | 1 | 2024-01-05 | 100 | paid
2 | 1 | 2024-02-10 | 150 | paid
3 | 2 | 2024-01-20 | 200 | paid
4 | 2 | 2024-03-02 | 50 | refunded
5 | 3 | 2024-02-14 | 300 | paid
6 | 1 | 2024-03-15 | 250 | paid
7 | 3 | 2024-03-20 | 120 | paid
8 | 2 | 2024-03-22 | 200 | paidExpected result
customer_id | order_id | order_date | amount
1 | 6 | 2024-03-15 | 250.0
2 | 8 | 2024-03-22 | 200.0
3 | 7 | 2024-03-20 | 120.0Common mistakes and limitations
- Using MAX(order_date) with GROUP BY and then selecting other columns: the other columns will not belong to that row. - Two orders on the same date: add a second sort column (here order_id) so the result is the same every time. - Very old database versions do not support CTEs or window functions.