Difference Between RANK() and DENSE_RANK() Functions in SQL
💡 Concept Name
RANK() and DENSE_RANK() – Both are ranking window functions used to assign ranks to rows within partitions, but they handle ties differently.
📘 Quick Intro
RANK() assigns ranks to rows with gaps in ranking when ties occur. DENSE_RANK() assigns ranks without gaps, even when ties occur.
🧠 Analogy / Short Story
Imagine a race where runners tie for second place: RANK() skips the next rank (jumping from 2 to 4), while DENSE_RANK() assigns the next rank immediately (2 then 3). This means RANK() leaves gaps in ranking numbers, but DENSE_RANK() does not.
🔧 Technical Explanation
RANK(): Assigns the same rank to ties but skips subsequent rank numbers, causing gaps.DENSE_RANK(): Assigns the same rank to ties but ranks subsequent rows consecutively without gaps.- Both are window functions used with
OVER()clause. - Useful for ranking items, leaderboards, or grouped analytics.
💻 Real Code Example
SELECT Name, Score,
RANK() OVER (ORDER BY Score DESC) AS Rank,
DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM Players;
-- Sample Output:
-- Name | Score | Rank | DenseRank
-- Alice | 100 | 1 | 1
-- Bob | 90 | 2 | 2
-- Carol | 90 | 2 | 2
-- Dave | 80 | 4 | 3
❓ Interview Q&A
Q1: What happens to rank numbers when there is a tie using RANK()?
A: The same rank is assigned, and subsequent rank(s) are skipped.
Q2: How does DENSE_RANK() handle ties?
A: It assigns the same rank without skipping subsequent ranks.
Q3: Can you use RANK() and DENSE_RANK() together?
A: Yes, they can be used side by side for comparison.
Q4: What clause do these functions require?
A: The OVER() clause with ordering.
Q5: Are these functions deterministic?
A: Yes, given the same ordering, they produce consistent results.
Q6: Can these functions be used with PARTITION BY?
A: Yes, to rank within groups.
Q7: Do these functions affect query performance?
A: They add computation but are optimized in most RDBMS.
Q8: What is the difference between RANK() and ROW_NUMBER()?
A: ROW_NUMBER() does not handle ties and assigns unique sequential numbers.
Q9: Can ties affect the total number of ranks?
A: Yes, RANK() can produce gaps while DENSE_RANK() does not.
Q10: Which function is better for continuous ranking?
A: DENSE_RANK() is better when you want no gaps in ranking.
📝 MCQs
Q1. What does RANK() do when there is a tie?
- Assigns different ranks
- Assigns same rank and skips next ranks
- Assigns same rank without skipping
- Assigns sequential numbers
Q2. How does DENSE_RANK() handle ties?
- Assigns different ranks
- Assigns same rank and skips next ranks
- Assigns same rank without skipping
- Assigns sequential numbers
Q3. Which function can produce gaps in ranking?
- DENSE_RANK()
- RANK()
- ROW_NUMBER()
- COUNT()
Q4. Do RANK() and DENSE_RANK() require an OVER() clause?
- No
- Yes
- Only RANK()
- Only DENSE_RANK()
Q5. Can these functions be used with PARTITION BY?
- No
- Yes
- Only in some DBMS
- Only with ORDER BY
Q6. What happens if there are no ties?
- Ranks are sequential
- Ranks have gaps
- Error
- NULL
Q7. What is the difference between RANK() and ROW_NUMBER()?
- No difference
- RANK() assigns unique numbers
- ROW_NUMBER() assigns unique sequential numbers
- DENSE_RANK() is same as ROW_NUMBER()
Q8. Are these functions deterministic?
- No
- Yes
- Sometimes
- Depends on data
Q9. Which function is better for continuous rank without gaps?
- RANK()
- DENSE_RANK()
- ROW_NUMBER()
- COUNT()
Q10. Can you use both RANK() and DENSE_RANK() together?
- No
- Yes
- Only one at a time
- Depends on DBMS
💡 Bonus Insight
Use RANK() when you want to identify positions with gaps after ties, useful in competitive ranking. Use DENSE_RANK() to assign ranks consecutively without gaps, useful for grouping tied results closely.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!