Difference between RANK() and DENSE_RANK()

The difference between RANK() and DENSE_RANK() lies in how they handle ties (rows with the same value) and the subsequent rankings assigned:

1. RANK():

• Assigns the same rank to rows with the same value (ties).
• Leaves a gap in the rankings after ties.

Here, since two rows share the same rank (2), the next rank is 4 (skips 3).

---

2. DENSE_RANK():

• Assigns the same rank to rows with the same value (ties).
• Does not leave gaps in the rankings after ties.

Here, after the ties at rank 2, the next rank is 3 (no gaps).

Usage:

• Use RANK() when you want rankings with gaps (e.g., competitions where skipping positions after ties is typical).
• Use DENSE_RANK() for continuous ranking without gaps (e.g., scenarios like finding the top N values).