RANK()

The RANK() function will assign a rank to each row within each partitioned result set. If multiple rows have the same values then each row with the same values will get the same rank numbers. However, after the same rank numbers or duplicate ranks next rank of the row will be skipped.


DENSE_RANK()

DENSE_RANK() function will assign a rank to each row within each partitioned result set. If multiple rows have the same value then each of these rows will share the same rank. However, the dense_rank of the following rows will not get skipped. 


ROW_NUMBER()

ROW_NUMBER() function will assign a unique row number to every row within each partitioned result set. It does not matter if the rows are duplicates or not.


Let’s look at the below sample table to explain the difference.


Table Name: Manager

id name salary
1 Jeff 8000
2 Elon 5000
3 Mukesh 5000
4 Warren 4000
5 Bill 3000


By using the manager table, let's write a query to get rank, dense rank, and row number for each row of the table:

SELECT *, 
           RANK() OVER( ORDER BY salary DESC) as ranks, 
           DENSE_RANK() OVER( ORDER BY salary DESC) as dense_ranks, 
           ROW_NUMBER() OVER( ORDER BY salary DESC) as row_numbers 
FROM manager;

 

id name salary ranks dense_ranks row_numbers
1 Jeff 8000 1 1 1
2 Elon 5000 2 2 2
3 Mukesh 5000 2 2 3
4 Warren 4000 4 3 4
5 Bill 3000 5 4 5

Check the difference between column values for ranks, dense_ranks and row_numbers.