ROW_NUMBER in SQL: Get the Latest Record per Customer
ROW_NUMBER numbers the rows inside each group, so you can keep just the newest one. Number each customer's orders from newest to oldest, then keep row one to get every customer's latest order, on a sample online shop database.
Step by step
Latest per customer
- One row per customer. Customers place many orders, but often you only want the latest one for each customer. Max gives you the date, but not the rest of the row. Row number gives you the whole row.
- The orders table. Each row in the orders table has a customer ID and an order date. Some customers have five orders, some just one.
Number the rows
- ROW_NUMBER. Row number, then over, opens a window. Partition by customer ID means each customer gets their own count, starting at one. Order by order date descending decides who gets number one: the newest order. We call the new column r n. All hundred and forty one orders are still here. But look at the r n column. Each customer counts one, two, three, and one is always their newest order.
SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER ( PARTITION BY customer_idORDER BY order_date DESC) AS rn FROM orders;
Keep row one
- Keep row one. You can't filter on row number in the same query's where. So wrap it in with, call it ranked, and keep the rows where r n equals one. Thirty four rows. One per customer, each with their latest order ID and date. Exactly what a last purchase report needs.
WITH ranked AS ( SELECT customer_id, order_id, order_date, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn FROM orders) SELECT * FROM ranked WHERE rn = 1;
First instead
- First order. Want each customer's first order instead? Swap descending for ascending. Now number one is the oldest order. Still thirty four customers, now each with the very first thing they ordered.
ASC
Recap
- Latest row per group. So, row number over, partition by the group, order by the date. Descending makes number one the newest. Then wrap it in with and keep row one.
