Question :- What is the main difference between RANK and DENSE_RANK functions in SQL?

Both RANK and DENSE_RANK functions are used to get the ranking of an ordered partition. Main difference between RANK and DENSE_RANK functions is in the handling of case when a tie happens while ranking the data. In a tie, the RANK function skips the next ranking(s) and assigns the same rank to values that tie. So there will be gaps in the rank. In a tie, the DENSE_RANK function does not skip the ranks. It assigns the same rank to values that tie. But the next rank will be a consecutive rank.

The RANK() function is a window function in SQL and it could be used to calculate a rank for each row within a partition of a result set.
Although rank is very useful function in sql but it does have one of the limitation or you can say that you will have a disadvantage that The same rank is assigned to the rows in a partition which have the same values. So if your data set have the same values then you can use the DENSE_RANK() or row_number() function instead of rank.

We use DENSE_RANK() function to specify a unique rank number within the partition as per the specified column value. It is similar to the Rank function with a small difference.
In the SQL RANK function DENSE_RANK(), if we have duplicate values, SQL assigns different ranks to those rows as well. Ideally, we should get the same rank for duplicate or similar values.

If you want to be better in Analytics, then do follow the below Playlist:-
Excel Playlist:-[ Ссылка ]

SQL Playlist:- [ Ссылка ]

Google Data Studio playlist:- [ Ссылка ]

You can follow us on the below social media handles:-
Blog: - [ Ссылка ]
Facebook:- [ Ссылка ]
Instagram: - [ Ссылка ]
LinkedIn: - [ Ссылка ]
Twitter: - [ Ссылка ]

свернуть