Difference between ROW Number, RANK, and DENSE RANK
ROW NUMBER:
This function works by assigning a continuous ranking to the records, without skipping any number in the result set, whether it is
partitioned or not.
Query to use ROW_NUMBER():
ROW_NUMBER() over (order by COL desc)
RANK:
This function returns a rank starting from 1 based on the ordering of rows and skips the ranking number if there is a tie.
Order by Clause is required while using the RANK function.
Partition by clause is optional, when we partitioned the data, rank is reset to 1 when the partition changes.
Query to use RANK():
RANK() over (order by COL desc)
DENSE_RANK:
This function returns a rank starting from 1 based on the ordering of rows and does not skip the ranking number if there is a tie.
Order by Clause is required while using the DENSE_RANK function.
Partition by clause is optional, when we partitioned the data, rank is reset to 1 when the partition changes.
Query to use DENSE_RANK():
DENSE_RANK() over (order by COL desc)
Thanks for Watching!
