ROW_NUMBER, RANK & DENSE_RANK

"Who scored the highest innings?", "What is Kohli's rank?", "Show each player's best innings." All of these need a position number. SQL has three ranking functions that differ in one thing only: how they treat ties. Our innings table has three ties (73, 59 and 45 each appear twice), which makes the difference easy to see.

Definition

A ranking function assigns a position number to each row based on the ORDER BY inside OVER, restarting at 1 for every partition.

• ROW_NUMBER() gives every row a unique sequential number (1, 2, 3, 4, 5…) — even tied rows get different numbers.
• RANK() gives tied rows the same rank, then skips the following numbers — leaving a gap (1, 2, 3, 3, 5…).
• DENSE_RANK() gives tied rows the same rank and does not skip — no gap (1, 2, 3, 3, 4…).

• Position based on ORDER BY — the ORDER BY decides who is 1st. It is required.
• Restarting per partition — with PARTITION BY player, each player has their own 1, 2, 3.
• The brackets are always empty: ROW_NUMBER(), not ROW_NUMBER(runs). Frames are not allowed.

Syntax

ROW_NUMBER() OVER ([PARTITION BY ...] ORDER BY sort_column [ASC | DESC])
RANK()       OVER ([PARTITION BY ...] ORDER BY sort_column [ASC | DESC])
DENSE_RANK() OVER ([PARTITION BY ...] ORDER BY sort_column [ASC | DESC])

Example — all three side by side

SELECT id, player, runs,
    ROW_NUMBER() OVER (ORDER BY runs DESC, id) AS row_no,
    RANK()       OVER (ORDER BY runs DESC)     AS rnk,
    DENSE_RANK() OVER (ORDER BY runs DESC)     AS dense_rnk
FROM innings
ORDER BY runs DESC, id;

How it works: (1) sort by runs, highest first; (2) number the rows.

• ROW_NUMBER: the two 73s get 4 and 5 — no ties ever.
• RANK: both 73s get 4, so the next score (59) jumps to 6 — rank 5 is skipped. RANK = "1 + number of rows strictly better than me".
• DENSE_RANK: both 73s get 4, and 59 gets 5 — no gap. DENSE_RANK = "1 + number of distinct better values".
• NULL: SQL Server treats NULL as the lowest value, so in DESC order Dhoni's NULL comes last (in ASC it would be first). Filter it out with WHERE runs IS NOT NULL if it should not be ranked.

Why , id in ROW_NUMBER? When two rows tie on runs, the database may number them in any order, and the order can change between runs. Adding a unique tie-breaker (id) makes ROW_NUMBER deterministic — always the same answer.

Which ranking function should I use?

Ask two questions:

1. Should tied values share a rank? → No → ROW_NUMBER() (unique numbers — perfect for "exactly one row per group", pagination, removing duplicates).
2. Yes → Should the ranking leave gaps after ties?
   • Yes → RANK() — sports-style ranking ("two players joint 4th, next is 6th").
   • No → DENSE_RANK() — "top 3 distinct scores", salary bands, levels.

Example — Top-N analysis: best innings per player

SELECT *
FROM (
    SELECT id, player, runs,
        ROW_NUMBER() OVER (PARTITION BY player ORDER BY runs DESC) AS rn
    FROM innings
) t
WHERE rn = 1;

Step by step: (1) rank the innings inside each player (PARTITION BY player); (2) keep only rank 1 in the outer query. Window results cannot be used in WHERE of the same query, so we wrap them in a subquery (or a CTE). Change rn = 1 to rn <= 2 for each player's top 2.

Common mistakes

• ❌ WHERE ROW_NUMBER() OVER (...) = 1 → ✅ wrap in a subquery/CTE and filter outside.
• ❌ Using ROW_NUMBER for "top 3 scores" when ties exist → a tied row is cut off arbitrarily ✅ use RANK or DENSE_RANK if ties must be included.
• ❌ RANK() OVER () → ✅ ORDER BY is required.
• ❌ Forgetting DESC → ORDER BY runs ranks the lowest score as 1.
• ❌ ROW_NUMBER over a non-unique ORDER BY → ✅ add a tie-breaker column for stable results.

Interview tip

Q: What is the difference between RANK and DENSE_RANK? A: Both give tied rows the same rank. RANK then skips numbers (1, 2, 2, 4) because it counts the tied rows; DENSE_RANK continues with the next number (1, 2, 2, 3). ROW_NUMBER never ties (1, 2, 3, 4).