Latest row per group in SQL Server, without guessing

“Show the latest order for each customer” has a hidden decision: what if two orders have the same timestamp? MAX(OrderDate) gives the timestamp but does not choose one row. Joining it back can return both tied orders.

The tie in a small example

WITH Orders(CustomerID, OrderID, OrderDate) AS (
    SELECT v.CustomerID, v.OrderID, CONVERT(datetime2(0), v.OrderDate)
    FROM (VALUES
        (1, 10, '2026-03-05T09:00:00'),
        (1, 11, '2026-03-05T09:00:00'),
        (1,  9, '2026-03-04T09:00:00'),
        (2, 20, '2026-03-03T12:00:00')
    ) AS v(CustomerID, OrderID, OrderDate)
), Latest AS (
    SELECT CustomerID, MAX(OrderDate) AS LastDate
    FROM Orders GROUP BY CustomerID
)
SELECT o.CustomerID, o.OrderID
FROM Orders AS o
JOIN Latest AS l ON l.CustomerID = o.CustomerID AND l.LastDate = o.OrderDate
ORDER BY o.CustomerID, o.OrderID;

Choose a tie-breaker deliberately

WITH Orders(CustomerID, OrderID, OrderDate) AS (
    SELECT v.CustomerID, v.OrderID, CONVERT(datetime2(0), v.OrderDate)
    FROM (VALUES
        (1, 10, '2026-03-05T09:00:00'),
        (1, 11, '2026-03-05T09:00:00'),
        (1,  9, '2026-03-04T09:00:00'),
        (2, 20, '2026-03-03T12:00:00')
    ) AS v(CustomerID, OrderID, OrderDate)
), Ranked AS (
    SELECT CustomerID, OrderID,
           ROW_NUMBER() OVER (
               PARTITION BY CustomerID ORDER BY OrderDate DESC, OrderID DESC
           ) AS rn
    FROM Orders
)
SELECT CustomerID, OrderID
FROM Ranked WHERE rn = 1
ORDER BY CustomerID;

Do not confuse order ID with time

OrderID DESC is a tie-breaker here, after OrderDate DESC. It does not prove that a higher ID was created later: imports, reseeding, delayed writes, and multiple systems can break that assumption. If the business rule is “latest by recorded timestamp, then highest ID”, document it as such. If the rule is “last committed”, a timestamp and identity column may still be insufficient to prove commit order.

Decide whether to keep ties

If the question asks for every order with the latest timestamp, the MAX-and-join result may be correct. RANK() or DENSE_RANK() with ORDER BY OrderDate DESC can also return all latest-date ties when filtered to rank 1. ROW_NUMBER() answers a different question: choose exactly one row per customer.

Customers with no orders do not appear in either query because the input is Orders. To include them, start from Customers and LEFT JOIN the ranked result. That is a separate reporting requirement, not a defect in the ranking function.

NULL dates need a policy

The examples have non-NULL dates. SQL Server sorts NULL below non-NULL values in ascending order, so ORDER BY OrderDate DESC usually places NULL dates last. If every order for a customer has a NULL date, ROW_NUMBER still chooses one using the tie-breaker, but MAX(OrderDate) returns NULL and the equality join shown earlier returns none. In either case, the word “latest” is not meaningful. Consider excluding undated rows, reporting them separately, or requiring a NOT NULL date in the source schema.

Include customers without orders when requested

The ranking examples start from Orders, which means customers without an order are absent. For a customer audit, rank orders in a CTE and LEFT JOIN the rn = 1 rows to Customers. Keep the rn filter in the JOIN condition; putting it in WHERE after a LEFT JOIN would remove customers with no match.

Check the report grain

Count the output rows and compare them with the number of customers that have at least one order. If the counts differ, investigate duplicates in the customer key, an incorrect partition key, or a join introduced after ranking. An index on (CustomerID, OrderDate DESC, OrderID DESC) may help some workloads, but inspect the actual execution plan before adding one.