Write a SQL query that returns the latest status-history row for every order.

Table: OrderStatusHistory(StatusHistoryId, OrderId, Status, ChangedAtUtc, ChangedByUserId). Return one complete status row per order. If timestamps tie, the larger StatusHistoryId is newer.

Order 7 has two history rows at the same timestamp with ids 40 and 41. Row 41 is returned because the tie-breaker makes the order deterministic.

• Return the status and actor from the same winning row.
• Use both timestamp and id for ordering.
• Return no more than one row per order.

I assign a row number inside each OrderId partition, newest first. Filtering to row number one returns the full winning row without joining back on a timestamp that may not be unique. The identity or sequence tie-breaker is part of the data contract, not cosmetic ordering.

WITH RankedStatus AS
(
    SELECT
        h.StatusHistoryId,
        h.OrderId,
        h.Status,
        h.ChangedAtUtc,
        h.ChangedByUserId,
        ROW_NUMBER() OVER
        (
            PARTITION BY h.OrderId
            ORDER BY h.ChangedAtUtc DESC, h.StatusHistoryId DESC
        ) AS rn
    FROM dbo.OrderStatusHistory AS h
)
SELECT StatusHistoryId, OrderId, Status, ChangedAtUtc, ChangedByUserId
FROM RankedStatus
WHERE rn = 1
ORDER BY OrderId;

An index on (OrderId, ChangedAtUtc DESC, StatusHistoryId DESC) including the projected columns aligns with partition and order. If the latest status is read constantly, keeping current status on Orders may be a useful read optimization while history remains authoritative for audit.

• Equal timestamps for one order.
• An order with exactly one history row.
• Many orders sharing the same timestamp.
• History imported out of chronological insertion order.

• Create timestamp ties and vary only the history id.
• Verify status and actor come from the same selected row.
• Use multiple partitions with different history lengths.

• Using MAX(ChangedAtUtc) and losing the rest of the row.
• Joining on the maximum timestamp and returning ties.
• Omitting the deterministic tie-breaker.
• Ranking all history when a maintained current-state column is the actual read model needed.