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 each customer's latest one. MAX(order_date) 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 5 orders, some just 1.
Number the rows
- ROW_NUMBER. Start with SELECT customer_id, order_id, order_date, then add ROW_NUMBER() OVER ( to open a window. PARTITION BY customer_id gives each customer their own count, starting at 1. ORDER BY order_date DESC decides who gets number 1: the newest order. Close the bracket, call the new column rn and finish with FROM orders;. Run it. All 141 orders are still there, but now the rn column counts 1, 2, 3 for each customer, and 1 is that customer's 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 WHERE of the same query. So wrap the query in WITH ranked AS ( ... ), then SELECT * FROM ranked WHERE rn = 1. It returns 34 rows: one per customer, each with their latest order_id and order_date. That's 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. To get each customer's first order instead, change DESC to ASC. Now number 1 is the oldest order. Run it again. There are still 34 customers, and each now shows the very first order they placed.
ASC
Recap
- Latest row per group. The pattern: ROW_NUMBER() OVER, PARTITION BY the group, ORDER BY the date. DESC makes number 1 the newest row and ASC makes it the oldest. Then wrap it in WITH and keep rn = 1.
Common mistakes
- Filtering on rn in the same query. WHERE runs before the window function, so WHERE rn = 1 in the same SELECT gives an error. Put the ROW_NUMBER query inside WITH (or a subquery) and filter in the outer SELECT.
- Leaving out PARTITION BY. Without PARTITION BY customer_id, ROW_NUMBER numbers the whole table from 1 to 141, so rn = 1 returns just 1 row. Partition by the column that defines each group.
- Two orders on the same date. ROW_NUMBER always gives different numbers, so on a tie it picks one row and the pick can change between runs. Add a tie-breaker, such as ORDER BY order_date DESC, order_id DESC.
- Empty order dates sorting to the top. Where NULLs go in a DESC sort depends on the database. PostgreSQL and Oracle put them first, so a NULL date can become rn 1. Filter them out with WHERE order_date IS NOT NULL inside the WITH.
When a business analyst uses this
A status history table gets a new row every time a requirement or defect changes status. Partition by the requirement ID, order by the change date descending and keep rn = 1. That gives each requirement's current status, with who changed it and when, for the sign-off report.
Questions people ask
What is the difference between ROW_NUMBER, RANK and DENSE_RANK in SQL?
ROW_NUMBER gives every row its own number, even on ties. RANK gives tied rows the same number and then skips (1, 1, 3), and DENSE_RANK gives ties the same number without skipping (1, 1, 2).
How do you use ROW_NUMBER to find duplicates in SQL?
Use PARTITION BY on the columns that should be unique. Rows with rn greater than 1 are the extra copies, and keeping rn = 1 shows one copy of each.
Does ROW_NUMBER work in SQL Server and Oracle?
Yes. The same ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) syntax works in SQL Server, Oracle, PostgreSQL and MySQL 8.0 or later. MySQL 5.7 and older don't have it.
