Latest Row Per Customer: Correct Ties and Efficient Plans

The latest order for each customer sounds like a simple MAX query. MAX can find the latest timestamp, but it does not identify a unique complete row when timestamps tie. Joining that timestamp back to orders can return several rows per customer, and independently taking MAX of other columns can assemble values that never belonged to one order.

Define what latest means

Choose the ordering contract first. This example uses OccurredAt descending and OrderId descending as a unique tie-breaker. That produces exactly one row when a customer has orders. The higher identity is only a tie-breaker here; it is not proof of later business time or later transaction commit.

CREATE TABLE #Customers(CustomerId int PRIMARY KEY);
INSERT #Customers VALUES(1),(2),(3);
CREATE TABLE #Orders(OrderId int PRIMARY KEY, CustomerId int NOT NULL,
 OccurredAt datetime2(0) NOT NULL, Amount decimal(10,2) NOT NULL);
INSERT #Orders VALUES(11,1,'20230101',10),(12,1,'20230101',20),
(13,2,'20230102',30);
CREATE INDEX IX_Latest ON #Orders(CustomerId,OccurredAt DESC,OrderId DESC)
INCLUDE(Amount);

;WITH ranked AS
(SELECT *, ROW_NUMBER() OVER(PARTITION BY CustomerId
 ORDER BY OccurredAt DESC,OrderId DESC) AS rn FROM #Orders)
SELECT c.CustomerId,r.OrderId,r.OccurredAt,r.Amount
FROM #Customers AS c LEFT JOIN ranked AS r
ON r.CustomerId=c.CustomerId AND r.rn=1
ORDER BY c.CustomerId;

Customer 1 has two orders at the same timestamp, so OrderId 12 wins by the declared rule. Customer 2 has one order, and customer 3 has none. The LEFT JOIN preserves customer 3 with NULL order fields. Keeping rn = 1 in the join condition is essential: moving it into WHERE would discard the unmatched customer.

If the requirement is all orders tied for latest time, this is the wrong rule. Use ranking by timestamp alone with RANK or DENSE_RANK and accept multiple results. Adding OrderId to the ranking order would remove the business tie. Decide between one representative and all equally recent rows before optimizing either query.

Compare two access strategies

ROW_NUMBER is convenient when retrieving results for many customers because SQL Server can process a broad order stream. OUTER APPLY provides a different formulation that can seek into the order index for each selected customer and stop after its first qualifying row.

SELECT c.CustomerId,o.OrderId,o.OccurredAt,o.Amount
FROM #Customers AS c
OUTER APPLY
(SELECT TOP(1) OrderId,OccurredAt,Amount FROM #Orders AS o
 WHERE o.CustomerId=c.CustomerId
 ORDER BY OccurredAt DESC,OrderId DESC) AS o
ORDER BY c.CustomerId;

Neither spelling is universally faster. A small selected customer set with a useful index often suits repeated seeks. A report covering nearly every customer may benefit from processing the child rows together. The optimizer can transform plans, so inspect actual execution, logical reads, and estimates rather than assuming syntax dictates the physical strategy.

The index starts with CustomerId, then follows the requested descending order, with Amount included for coverage. That can avoid a sort or extra lookup for this access pattern. Additional columns increase index size and write cost; include only fields justified by the query workload. Missing or poorly ordered indexes can make an APPLY plan repeatedly scan large portions of the child table.

Test the contract under real data

Distinguish latest successful order from latest order whose status is successful. Filtering to success before ranking selects the most recent successful event. Ranking everything first and filtering afterward can return no result when the newest event failed. Both interpretations are plausible, but they answer different business questions.

An as-of report also needs a cutoff in the candidate set. Apply OccurredAt less than the exclusive cutoff before selecting the winner. If several statements must agree on the same database view, define the isolation boundary; independently executed queries can observe changes between steps.

Test empty child sets, timestamp ties, one extremely busy customer, many quiet customers, and the status-filter distinction. Check that the output contains the expected number of customers and that every selected Amount belongs to the returned OrderId. The useful optimization preserves a clearly defined row-selection rule while reducing the work required to find that row.

Technical references: Microsoft Learn: ROW_NUMBER · Microsoft Learn: TOP.