Use ROW_NUMBER to Get the Latest Order per Customer
Use ROW_NUMBER to Get the Latest Order per Customer
Order Table:
Query Explanation:
-
ROW_NUMBER() OVER (...)assigns a unique row number to each order per customer, based on latestOrderDatefirst (DESC). -
PARTITION BY CustomerIDgroups the rows by each customer so that numbering resets for each one. -
ORDER BY OrderDate DESCensures the latest order getsROW_NUMBER = 1. -
The outer query filters
WHERE rn = 1, which means only the latest order of each customer is selected.
SQL Query:
SELECT *
FROM (
SELECT
OrderID,
CustomerID,
OrderDate,
ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
FROM Orders
) AS RankedOrders
WHERE rn = 1;
Output:
