ROW_NUMBER vs RANK vs DENSE_RANK

 

ROW_NUMBER vs RANK vs DENSE_RANK

All three are window (analytic) functions used for ranking rows.

---

🔹 Sample Data

emp	dept	salary
A	IT	9000
B	IT	9000
C	IT	8000
D	IT	7000

---

🔹 ROW_NUMBER()

• Assigns unique number to each row
• No ties (even if values are same)

SELECT emp, salary,

       ROW_NUMBER() OVER (ORDER BY salary DESC) AS rn

FROM emp_table;

Output:

emp	salary	rn
A	9000	1
B	9000	2
C	8000	3
D	7000	4

➡ Always unique numbering.

---

🔹 RANK()

• Same values get same rank
• Gaps appear in ranking

SELECT emp, salary,

       RANK() OVER (ORDER BY salary DESC) AS rnk

FROM emp_table;

Output:

emp	salary	rnk
A	9000	1
B	9000	1
C	8000	3
D	7000	4

➡ Rank 2 is skipped

---

🔹 DENSE_RANK()

• Same values get same rank
• No gaps in ranking

SELECT emp, salary,

       DENSE_RANK() OVER (ORDER BY salary DESC) AS drnk

FROM emp_table;

Output:

emp	salary	drnk
A	9000	1
B	9000	1
C	8000	2
D	7000	3

➡ No missing rank numbers

---

🔹 Key Differences (Quick Table)

Function	Handles Ties	Gaps in Rank	Unique Values
ROW_NUMBER	❌ No	❌ No	✅ Yes
RANK	✅ Yes	✅ Yes	❌ No
DENSE_RANK	✅ Yes	❌ No	❌ No

---

🔹 When to Use What

✔ Use ROW_NUMBER

• Remove duplicates
• Pick exactly one record
• Deduplication logic

✔ Use RANK

• Competition-style ranking
• Reports where rank gaps matter

✔ Use DENSE_RANK

• Top-N per group
• Business ranking without gaps

---

🔹 ETL / Data Engineering Example

Top 2 Salaries per Department:

SELECT *

FROM (

  SELECT emp, dept, salary,

         DENSE_RANK () OVER (

           PARTITION BY dept

           ORDER BY salary DESC

         ) AS dr

  FROM emp_table

) t

WHERE dr <= 2;

---

ROW_NUMBER gives unique numbers, RANK allows gaps, and DENSE_RANK removes gaps while handling ties.

---

Top of Form

 

Bottom of Form