RANK() versus DENSE RANK()

RANK() versus DENSE RANK()

Both of them are OLAP(Online Analytical Processing) functions.There is a difference between these two functions in assigning the ranks.

RANK function leaves the gaps between ranks when there is a same rank applied for multiple rows.

DENSE RANK functions applies the rank without leaving any gaps,though the same rank is applied for multiple rows.

SELECT EMPNO,ENAME,SAL,

RANK() OVER(ORDER BY SAL DESC) AS RANK,

DENSE_RANK() OVER(ORDER BY SAL DESC) AS DENSE_RANK

FROM EMP;

Note:The difference is precisely highlighted in the adjacent figure.

Same function can be applicable for Teradata database in TD14.