Rank your Row Number but don't make it dense!
In the last post, we saw that there are different kinds of Functions we can use while building a window function query. Let’s look at the difference between Rank, Dense Rank and Row Number, which is quite frankly very confusing but also most asked question during SQL Interviews:
ROW_NUMBER() counts the records in serial order and never repeat any number in case of a tie e.g. 1,2,3,4,5
RANK() counts the records in order but if there is any tie then it repeats the order number but skips the next number in the order e.g. 1,2,2,4,5
DENSE_RANK() counts the records in order but if there is any tie then it repeats the order number but does not skip the next number in the order e.g. 1,2,2,3,4
So, when do we use one over another? Some examples :-
ROW_NUMBER(): When fetching the top N salespersons per region regardless of ties in sales amounts.
RANK(): Ranking students based on test scores where ties are okay, but gaps in ranking numbers (e.g., two 1st places, next rank is 3rd) are expected.
DENSE_RANK(): Ranking players in a game by score where ties are expected, but you want the ranks to be dense, without skipping any rank numbers.
Sample query with results as:
