RANK vs DENSE_RANK

RANK vs DENSE_RANK in SQL (With a Real Interview Question)

One of the most confusing yet frequently asked SQL interview topics is the difference between
👉 RANK() and DENSE_RANK() window functions.

At first glance, both look similar. But a small difference in how they handle duplicate values can completely change your query result—especially in Nth highest salary problems.

Let’s break it down with a real-world interview-style example.

🧠 Problem Statement

👉 Find the SECOND highest salary from an employees table.

Sample salary data:

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

🔍 Attempt 1: Using RANK()

SELECT salary
FROM (
    SELECT salary,
           RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk = 2;

❌ What goes wrong with RANK()?

RANK() assigns the same rank to duplicate values, but it also skips the next rank.

Ranking result:

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

👉 Notice the problem? Rank 3 does not exist.

If you query:

WHERE rnk = 3

You’ll get no rows, which is a common interview trap.

✅ Correct & Optimized Approach: Using DENSE_RANK()

SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk = 2;

✔ Why DENSE_RANK() works better

DENSE_RANK() also assigns the same rank to duplicates—but does not skip ranks.

Ranking result:

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

This makes DENSE_RANK() perfect for Nth highest / lowest problems.

📌 Key Difference (Interview Gold)

👉 Rule of Thumb:
Use DENSE_RANK() when solving Nth highest / lowest value queries.

💡 When Should You Use RANK()?

Use RANK() when:

• Rank gaps actually matter
• You’re building leaderboards
• You want to reflect true competition ranking (e.g., Olympics-style ranking)

💬 Interview Question for You

When would you prefer RANK() over DENSE_RANK() in real-world systems?
Think analytics, reporting, or business dashboards.

Drop your thoughts in the comments 👇