DIFFERENCE BETWEEN RANK AND DENSE_RANK IN SQL

DENSE_RANK() and RANK()  are both aggregate functions.

The difference between the DENSE_RANK() and RANK() functions is that RANK will assign non-consecutive ranks to the values in a set in the case where the values are same, which means with RANK() there will be gaps between the ranking when the values are same.

The DENSE_RANK() will assign consecutive ranks to the values in the case where the values are same, so there will be no gaps between the ranking when the values are same.


Example:

select ename,sal,
rank() over (order by sal desc)  rank,
dense_rank() over (order by sal desc) dense_rank
from emp;