The latest-record pattern identifies the most recent row for each entity, often using ROW_NUMBER, MAX, or a correlated subquery.
Business systems frequently contain multiple historical records and require only the most recent status or transaction.
Find each customer's latest order.
WITH RankedOrders AS (
SELECT
o.*,
ROW_NUMBER() OVER (
PARTITION BY o.CustomerID
ORDER BY o.OrderDate DESC, o.OrderID DESC
) AS rn
FROM Orders o
)
SELECT
c.CustomerName,
r.OrderID,
r.OrderDate,
r.OrderAmount
FROM Customers c
INNER JOIN RankedOrders r
ON c.CustomerID = r.CustomerID
WHERE r.rn = 1;Used in customer status, latest transaction, employee history, account activity, and reporting systems.
- ROW_NUMBER is useful for latest-record logic.
- PARTITION BY groups records by entity.
- ORDER BY DESC places newest records first.
- A second sort column can make results deterministic.
- Filtering rn = 1 returns the latest row.
How do you find the latest record per customer?
Use ROW_NUMBER partitioned by customer and ordered by the relevant date descending, then filter for row number 1.
Why add OrderID to the ORDER BY?
It provides a deterministic tie-breaker when multiple records have the same date.