ROW_NUMBER vs RANK vs DENSE_RANK in SQL (The Complete Interview Guide)

If you’re preparing for SQL interviews or working on analytics and data engineering problems, you will encounter this question:

👉 What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

They all look similar, but each solves a different business problem. Let’s understand them clearly with examples.

🧠 Problem Statement

Assume we have an employees table with the following salaries:

  • 90,000
  • 80,000
  • 80,000
  • 70,000

We want to assign a ranking based on salary (highest first).

🔢 1. ROW_NUMBER()

SELECT salary,
       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn
FROM employees;

📌 How ROW_NUMBER() behaves

  • Assigns a unique number to every row
  • Ignores duplicates completely

Result:

  • 90,000 → 1
  • 80,000 → 2
  • 80,000 → 3
  • 70,000 → 4

✔ Best when:

  • You want unique rows
  • You need pagination
  • You want the top N rows without caring about ties

🏁 2. RANK()

SELECT salary,
       RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees;

📌 How RANK() behaves

  • Same values get the same rank
  • Skips ranks after duplicates

Result:

  • 90,000 → Rank 1
  • 80,000 → Rank 2
  • 80,000 → Rank 2
  • 70,000 → Rank 4 ❌

✔ Best when:

  • Rank gaps are important
  • You are building competition-style leaderboards

🎯 3. DENSE_RANK()

SELECT salary,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk
FROM employees;

📌 How DENSE_RANK() behaves

  • Same values get the same rank
  • No gaps in ranking

Result:

  • 90,000 → Rank 1
  • 80,000 → Rank 2
  • 80,000 → Rank 2
  • 70,000 → Rank 3 ✅

✔ Best when:

  • Solving Nth highest / lowest problems
  • You want continuous ranking

📊 Side-by-Side Comparison (Interview Favorite)

Function Handles duplicates Skips ranks Use case
ROW_NUMBER() ❌ No ❌ No Pagination, top-N rows
RANK() ✅ Yes ✅ Yes Leaderboards
DENSE_RANK() ✅ Yes ❌ No Nth highest salary

💡 Real Interview Tip

👉 Nth highest / lowest value?
Always think DENSE_RANK()

👉 Top N rows strictly?
Use ROW_NUMBER()

👉 Competition or leaderboard ranking?
Use RANK()

💬 Interview Question for You

How would you find the top 2 salaries per department, considering duplicates?

(Hint: Window functions + partitioning 😉)