365 Days of Daily Coding: Day 111

Why would I use DENSE_RANK instead of RANK? What about RANK instead of DENSE_RANK?

DENSE_RANK and RANK are functions used to rank records. The only key difference between them is that while RANK skips numbers/rank in case of ties whereas DENSE_RANK doesn’t skip numbers/rank and assigns consecutive numbers.

In real word scenario, we can use both to identify the nth highest salary or certain metric, however, the business case for how to treat in case of ties would differentiate which functions to use over the other.

Let’s say we were asked to find the top 3 salesperson. The first rank is tied between two salesperson.

1. Use RANK() when it is important only to identify the 3rd highest regardless if there are ties or not.
2. Use DENSE_RANK() when it is important to identify the 3rd highest taking into account that there could be some salesperson who have same rank.

The other example I could think of:

1. Use RANK() when management want to only appraise 3 people who sold highest. What if there are more than 3 people for Rank 1? This could be a challenge and unfair for those despite selling the same.
2. Use DENSE_RANK() when management want to appraise the top 3 ranks; there is no limit to the number of people that could get the appraisal.