Database.Middle.What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

Great question! ROW_NUMBER(), RANK(), and DENSE_RANK() are ranking window functions in SQL that assign a number to each row based on some ordering criteria.

They look similar but behave differently when there are ties in the data (i.e., rows with the same value in the ORDER BY column).

📊 Quick Comparison

Function	Handles Ties?	Gaps in Rank?	Always Unique?
ROW_NUMBER()	❌ No	✅ Yes	✅ Yes
RANK()	✅ Yes	✅ Yes	❌ No (may repeat)
DENSE_RANK()	✅ Yes	❌ No	❌ No (may repeat)

---

🔍 Visual Example

Let’s say we have this table ordered by score DESC:

name	score
Alice	100
Bob	90
Carol	90
Dave	80

Query:

SELECT
  name,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC) AS row_num,
  RANK()       OVER (ORDER BY score DESC) AS rank,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_rank
FROM scores;

📈 Output:

name	score	row_num	rank	dense_rank
Alice	100	1	1	1
Bob	90	2	2	2
Carol	90	3	2	2
Dave	80	4	4	3

🧠 Key Differences Explained

✅ ROW_NUMBER()

• Ignores ties.
• Simply numbers rows sequentially.
• Always unique.

✅ RANK()

• Gives same rank to tied rows.
• Skips the next rank(s) to maintain total ordering.

Two rows tied at rank 2 → next row gets rank 4 (gap).

✅ DENSE_RANK()

• Also gives same rank to tied rows.
• Does not skip the next rank.

Two rows tied at rank 2 → next row gets rank 3.

---

📦 When to Use Each

Use Case	Recommended Function
Need unique row index	ROW_NUMBER()
Ranking with gaps (like sports)	RANK()
Ranking without gaps	DENSE_RANK()
Pagination with ordering	ROW_NUMBER()