What is the main difference between RANK and DENSE_RANK functions in Oracle?

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, RANK function skips the next ranking(s) and assigns same rank to values that tie. So there will be gaps in the rank.

In a tie, DENSE_RANK function does not skip the ranks. It assigns same rank to values that tie. But next rank will be consecutive rank.

E.g. In set (10, 10, 20, 30, 30, 40), RANK returns (1,1,3,4,4,6)
DENSE_RANK returns (1,1,2,3,3,4)

Read the full book at www.amazon.com
Posted in SQL, SQL Interview Questions

Leave a Reply

Your email address will not be published. Required fields are marked *

*