SQL Ranking Functions: RANK, DENSE_RANK, ROW_NUMBER
💡 Concept Name
Ranking functions in SQL assign a unique number to rows based on their position in a result set. The most used are ROW_NUMBER(), RANK(), and DENSE_RANK().
📘 Quick Intro
These functions are part of SQL's window functions and help you rank data within partitions using OVER() clause with ORDER BY. They're useful for reporting, pagination, and finding top records.
🧠 Analogy / Short Story
Think of students lining up by test scores. ROW_NUMBER gives everyone a unique spot, even if scores tie. RANK skips numbers for ties, like two people at 1st, next gets 3rd. DENSE_RANK gives ties the same rank but continues without gaps. Like placing medals!
🔧 Technical Explanation
ROW_NUMBER(): Assigns a unique sequential number regardless of duplicates.RANK(): Gives the same rank to ties but skips the next rank(s).DENSE_RANK(): Gives the same rank to ties without skipping ranks.- Used with
OVER (PARTITION BY ... ORDER BY ...)syntax. - Ideal for pagination, leaderboards, and deduplication tasks.
🎯 Purpose & Use Case
- ✅ Getting top N results per category.
- ✅ Assigning sequential numbers to rows.
- ✅ Handling duplicates with clear rank distinction.
- ✅ Building complex business reports.
💻 Real Code Example
SELECT Name, Score,
ROW_NUMBER() OVER (ORDER BY Score DESC) AS RowNum,
RANK() OVER (ORDER BY Score DESC) AS RankNum,
DENSE_RANK() OVER (ORDER BY Score DESC) AS DenseRank
FROM Students;
❓ Interview Q&A
Q1: What is the purpose of SQL ranking functions?
A: To assign positions to rows in a result set based on ordering logic.
Q2: What does ROW_NUMBER() do?
A: Assigns a unique number to each row regardless of duplicates.
Q3: When should you use DENSE_RANK() over RANK()?
A: When you want no gaps in ranking even if there are ties.
Q4: What clause must be used with ranking functions?
A: The OVER() clause with an ORDER BY.
Q5: Can you partition ranks by a column?
A: Yes, use PARTITION BY in the OVER() clause.
Q6: What’s the key difference between RANK and DENSE_RANK?
A: RANK skips numbers for ties; DENSE_RANK does not.
Q7: What is a use case for ROW_NUMBER()?
A: Paginating results or removing duplicates.
Q8: Do ranking functions modify the dataset?
A: No, they only return ranking info in result columns.
Q9: Can you use ranking functions in WHERE clause?
A: Not directly—you need a subquery or CTE.
Q10: What happens if two rows have the same ordering column value?
A: ROW_NUMBER still assigns different numbers; RANK and DENSE_RANK give the same rank.
📝 MCQs
Q1. Which ranking function skips numbers after ties?
- ROW_NUMBER()
- DENSE_RANK()
- RANK()
- NTILE()
Q2. Which function guarantees no ranking gaps?
- RANK()
- ROW_NUMBER()
- NTILE()
- DENSE_RANK()
Q3. Which function always gives unique ranking?
- DENSE_RANK()
- RANK()
- ROW_NUMBER()
- ORDER()
Q4. What is required with ranking functions?
- JOIN
- GROUP BY
- OVER() clause
- HAVING
Q5. Which clause divides ranking into groups?
- GROUP BY
- DIVIDE BY
- PARTITION BY
- SEGMENT BY
Q6. What happens with RANK() when two rows tie?
- Same rank, no skip
- Different ranks
- Same rank, skip next number
- Error
Q7. What clause determines row order?
- WHERE
- GROUP BY
- ORDER BY
- SELECT
Q8. Which ranking function is best for deduplication?
- RANK()
- ROW_NUMBER()
- NTILE()
- DENSE_RANK()
Q9. Can you use RANK without OVER()?
- Yes
- No
- Only in MySQL
- Only in joins
Q10. What’s the output of ROW_NUMBER with no ORDER BY?
- Random numbers
- Error or arbitrary result
- Zeros
- Duplicate rows
💡 Bonus Insight
You can combine ranking functions with CTEs or subqueries to filter top-N results or remove duplicates efficiently. Be mindful that performance depends on sort keys and partition size.
📄 PDF Download
Need a handy summary for your notes? Download this topic as a PDF!