RANK and DENSE_RANK Function

RANK and DENSE_RANK both provide rank to the records based on some column value or expression. In case of a tie of 2 records at position N, RANK declares 2 positions N and skips position N+1 and gives position N+2 to the next record. While DENSE_RANK declares 2 positions N but does not skip position N+1.

Query shows the usage of both RANK and DENSE_RANK. For DEPTNO 20 there are two contenders for the first position (EMPNO 7788 and 7902). Both RANK and DENSE_RANK declares them as joint toppers. RANK skips the next value that is 2 and next employee EMPNO 7566 is given the position 3. For DENSE_RANK there are no such gaps.

SELECT empno, deptno, sal,

RANK() OVER (PARTITION BY deptno

ORDER BY sal DESC NULLS LAST) RANK,

DENSE_RANK() OVER (PARTITION BY

deptno ORDER BY sal DESC NULLS

LAST) DENSE_RANK

FROM emp

WHERE deptno IN (10, 20)

ORDER BY 2, RANK;

EMPNO  DEPTNO   SAL  RANK DENSE_RANK

------ ------- ----- ----- ----------

  7839      10  5000     1          1

  7782      10  2450     2          2

  7934      10  1300     3          3

  7788      20  3000     1          1

  7902      20  3000     1          1

  7566      20  2975     3          2

  7876      20  1100     4          3

  7369      20   800     5          4

8 rows selected.

Query (RANK and DENSE_RANK example)