When two records have the same score, which one should come first? ORDER BY Score DESC is valid SQL without answering that question. In the ranking queries from my archive, ROW_NUMBER is useful only when the ordering rule is explicit enough to explain to the person reading the result.
Choose a complete tie-break rule
The example orders by higher score, earlier date and finally smaller ID. Prioritizing the earlier date is a demonstration rule, not a universal assessment policy. The ID resolves any remaining tie deterministically. A real process must agree on that policy before a query turns it into a result.
WITH Scores AS (
SELECT * FROM (VALUES
(1,'A',80,CAST('2026-01-02' AS date)),
(2,'A',80,CAST('2026-01-01' AS date)),
(3,'B',90,CAST('2026-01-01' AS date))
) d(Id,Team,Score,CreatedAt)
)
SELECT Id,
ROW_NUMBER() OVER(ORDER BY Score DESC,CreatedAt,Id) AS OverallRank,
ROW_NUMBER() OVER(PARTITION BY Team ORDER BY Score DESC,CreatedAt,Id) AS TeamRank
FROM Scores ORDER BY OverallRank;The overall order is IDs three, two and one. Three has ninety points; the other two have eighty, and two has the earlier date. Within TeamRank, B's only record is first while A's records are ranked relative to each other. OverallRank and TeamRank therefore describe different scopes.
PARTITION BY preserves the detail rows
PARTITION BY Team restarts the numbering for each team. It does not reduce the result to one row per team as GROUP BY would. This allows one result row to retain its identity while carrying both an overall position and a position within its group.
The ORDER BY inside OVER determines the assigned number. The final ORDER BY determines presentation order of the returned result. They are not interchangeable guarantees. If the screen should display the calculated ranking order, the outer query must request it as well.
Decide whether equal scores should share a rank
ROW_NUMBER assigns a different number to every row. RANK and DENSE_RANK can represent shared positions, but adding a unique ID to their ranking expression removes the tie. A shared business rank and a stable display order may therefore need different ordering expressions.
For scores 90,80,80,70 ordered by score alone, RANK yields 1,2,2,4 and DENSE_RANK yields 1,2,2,3. The gap is part of the contract, not cosmetic punctuation. It matters if rank later controls qualification or capacity decisions.
Filtering can change the meaning of the rank
WHERE is applied before window functions. Filtering to team A first calculates a position among A's records rather than among all records. To preserve an already calculated overall rank while showing only A, calculate it in an inner query and filter outside. Authorization scope remains a separate consideration; records the user must not use should not automatically enter the calculation.
A row number can also change whenever the underlying result changes. It is not a durable record ID and must not become an update key. Pagination needs the same unique ordering across requests, with an explicit strategy for concurrent changes where a stable snapshot is required.
Verify the policy on deliberately tied input
Give both eighty-point records the same date and observe how ID resolves the tie. Then filter one team and compare the intended meanings of overall and team rank. On large tables, inspect sort operations and suitable indexes in the execution plan; the presence of a window function alone is not a performance measurement.