ROW_NUMBER vs RANK vs DENSE_RANK — the difference is what happens on a tie

All three functions produce 1, 2, 3, … over ordered rows — they only disagree when rows tie. On a tie, ROW_NUMBER invents an order, RANK repeats-then-skips, DENSE_RANK repeats-without-skipping. That one sentence answers the interview question; the rest of this post shows it happening on real salary data, and which function each real task wants.

The one table that shows everything

SQL Quest's employees table has two real ties near the top: two people at 98,000 and two at 95,000. Run all three functions side by side:

SELECT name, salary,
  ROW_NUMBER() OVER (ORDER BY salary DESC) AS row_num,
  RANK()       OVER (ORDER BY salary DESC) AS rnk,
  DENSE_RANK() OVER (ORDER BY salary DESC) AS dense
FROM employees
ORDER BY salary DESC;

Read the 98,000 tie: ROW_NUMBER says 5 and 6 — somebody had to be "first", and which one is arbitrary and unstable unless you add a tiebreaker. RANK gives both 5, then skips to 7 — because two people are ahead of Alice, Olympic style. DENSE_RANK gives both 5, then 6 — no gaps, it counts distinct salary levels.

Which one does your task want?

• "Give me exactly N rows" → ROW_NUMBER, with a tiebreaker. Pagination, dedup, top-10 lists that must be exactly 10. Always add a deterministic tiebreak (ORDER BY salary DESC, emp_id) — without it, ties resolve differently between runs and your "same" query returns different rows.
• "Competition placing" → RANK. If two athletes tie for gold, the next one gets bronze (rank 3). Use where the number of people ahead of you is the meaning.
• "How many distinct levels" → DENSE_RANK. "Second-highest salary" questions are DENSE_RANK questions: at the 95,000 row it says 6 distinct salary values exist above-or-at this row — no phantom gaps.

The top-N-per-group pattern

The most-asked window question in analytics interviews — "highest-paid person per department" — is one CTE plus one ranking function:

WITH ranked AS (
  SELECT name, department, salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT department, name, salary FROM ranked WHERE rn = 1;

PARTITION BY department restarts the numbering per department; WHERE rn = 1 keeps each winner. Change = 1 to <= 3 for top-3; change ROW_NUMBER to RANK if tied winners should both appear.

▶ Practice: Rank Everyone by Salary

FAQ

Why does RANK skip numbers?

Because it answers "how many rows are ahead of me, plus one." After a two-way tie at 5, two rows are ahead of the next person — so they are rank 7. The gap carries information: it preserves head-counts.

Do I need PARTITION BY for these functions?

No — without it the window is the whole result set (one global ranking). PARTITION BY restarts the numbering per group. ORDER BY inside OVER, however, is required: an unordered ranking is meaningless, and most engines reject it.

NTILE — the fourth sibling?

NTILE(4) deals rows into 4 equal buckets — quartiles. It answers "which segment", not "which position". Same OVER machinery, different question.