T-SQL reference › ROW_NUMBER

ROW_NUMBER in SQL Server: the latest row per group

MAX gives you the latest date per customer. When you want the whole latest row (its ID and amount too) you number the rows and keep number 1.

Number each customer’s orders

SELECT customer_id, order_id, order_date,
       ROW_NUMBER() OVER (PARTITION BY customer_id
                          ORDER BY order_date DESC, order_id DESC) AS rn
FROM Orders;

PARTITION BY restarts the numbering for each customer. ORDER BY decides who gets 1. The second sort column settles ties, so two orders on the same day don’t both claim first place at random.

Keep number 1

WITH ranked AS (
  SELECT customer_id, order_id, order_date, total_amount,
         ROW_NUMBER() OVER (PARTITION BY customer_id
                            ORDER BY order_date DESC, order_id DESC) AS rn
  FROM Orders
)
SELECT customer_id, order_id, order_date, total_amount
FROM ranked
WHERE rn = 1
ORDER BY customer_id;

“Invalid column name 'rn'”

Adding WHERE rn = 1 to the first query fails. SQL Server works out WHERE before SELECT, so the alias doesn’t exist yet, and window functions aren’t allowed in WHERE anyway. Wrap it in a CTE or subquery and filter outside.

ROW_NUMBER, RANK or DENSE_RANK?

ROW_NUMBER always gives 1, 2, 3. RANK gives ties the same number and skips (1, 1, 3). DENSE_RANK gives ties the same number without gaps (1, 1, 2). For “one row per group” you want ROW_NUMBER.

Try it on Harbour & Co

Kwame Asante (Data Analyst) needs this now: “For each customer, their single latest order: id, date and amount.” Write the query, run it, send the results. On the paid plan.

Take the ticket →