ROW_NUMBER, RANK, DENSE_RANK and top-N per group

Part of the free LogicWiz SQL & Databases course, Module 11: Window functions. The queries and practice run in the app on real Postgres.

Four players were bought for exactly ₹18 crore. Is the next player 3rd, 6th or does it depend? Meher's leaderboard said one thing, Aarav's said another, and both queries were "correct".

You'll learn to

• Number rows with ROW_NUMBER, RANK and DENSE_RANK and predict what ties do
• Pick the right one for top-N, Nth highest and removing duplicates
• Write top-N per group with PARTITION BY and an outer filter
• Make a ranking deterministic with a tie-breaker

Learn

Three ways to count positions

All three need ORDER BY inside OVER, and all three give 1 to the first row. They differ only on ties:

• ROW_NUMBER() gives 1, 2, 3, 4... no matter what. Tied rows get different numbers.
• RANK() gives tied rows the same number, then skips: 1, 2, 2, 2, 2, 6. Like a sports table.
• DENSE_RANK() gives tied rows the same number and does not skip: 1, 2, 2, 2, 2, 3.

All three side by side

SELECT name, price_cr,
       ROW_NUMBER() OVER (ORDER BY price_cr DESC, name) AS row_num,
       RANK()       OVER (ORDER BY price_cr DESC) AS rnk,
       DENSE_RANK() OVER (ORDER BY price_cr DESC) AS dense_rnk
FROM players
ORDER BY price_cr DESC, name;

Bumrah, Gaikwad, Jadeja and Rashid all cost 18.00. RANK gives all four a 2 and jumps to 6 for Axar. DENSE_RANK gives them 2 and Axar 3. ROW_NUMBER gives 2, 3, 4, 5, using name to break the tie.

The same prices, three numberings

RANK skips to 6 because five players are ahead. DENSE_RANK counts distinct values ahead.

ROW_NUMBER() OVER (ORDER BY price_cr DESC) with ties is not stable: which ₹18 crore player gets 2 can change between runs. Always add a tie-breaker (ORDER BY price_cr DESC, name). And never use ROW_NUMBER for "top 3" when ties should be kept: it silently drops a tied player.

Top-N per group

"Top 2 run-scorers in each team" is the most asked window question. The recipe:

1. Rank inside each group: RANK() OVER (PARTITION BY team ORDER BY runs DESC).
2. Do it in a subquery, because WHERE cannot see a window.
3. Outside, keep WHERE rnk <= 2.

PARTITION BY restarts the numbering at 1 for every team.

Highest score in each match

SELECT match_id, name, runs
FROM (
  SELECT b.match_id, p.name, b.runs,
         RANK() OVER (PARTITION BY b.match_id ORDER BY b.runs DESC) AS rnk
  FROM batting b
  JOIN players p ON p.player_id = b.player_id
) ranked
WHERE rnk = 1
ORDER BY match_id;

RANK keeps both players if two tie for the top score of a match. Change it to ROW_NUMBER and only one would survive, picked at random.

Which one do I use?

"Find the second highest salary" is the classic. With DENSE_RANK() ... = 2 you get the second highest distinct value even if two people share the top. Also asked: "delete duplicate rows, keeping one" (ROW_NUMBER per duplicate group, delete where it is above 1).

In one line

ROW_NUMBER never ties, RANK ties and skips, DENSE_RANK ties without gaps; top-N per group = rank with PARTITION BY in a subquery, filter outside.

Interview corner

Explain ROW_NUMBER vs RANK vs DENSE_RANK.

All number rows in the window's ORDER BY. On ties: ROW_NUMBER still gives 1, 2, 3; RANK gives 1, 1, 3 (same number, then skips); DENSE_RANK gives 1, 1, 2 (same number, no gap). Pick ROW_NUMBER for exactly one row, RANK for leaderboards, DENSE_RANK for Nth distinct value.

Find the Nth highest salary.

Rank distinct salaries with DENSE_RANK and filter: SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) r FROM employees) t WHERE r = N;. It handles ties at the top, unlike LIMIT 1 OFFSET N-1 on non-distinct rows.

Get the top 3 earners in every department.

Rank inside each department and filter outside: SELECT * FROM (SELECT *, DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) r FROM employees) t WHERE r <= 3;. Say whether ties are kept (RANK/DENSE_RANK) or not (ROW_NUMBER).

Delete duplicate rows but keep one copy.

Number the copies with ROW_NUMBER() OVER (PARTITION BY the duplicate columns ORDER BY id) and delete where it is above 1: DELETE FROM t WHERE id IN (SELECT id FROM (SELECT id, ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) rn FROM t) x WHERE rn > 1);.