Dense_Rank

Dense_Rank

DENSE_RANK computes the rank of a row in an ordered group of rows and returns the rank as a NUMBER. The ranks are consecutive integers beginning with 1.

Syntax:

DENSE_RANK( expression ) WITHIN GROUP ( ORDER BY expression )

DENSE_RANK( ) OVER ([query_partition_clause] order_by_clause)

Example 1:

select DENSE_RANK(1000, 300) WITHIN GROUP (ORDER BY SAL, COMM)
from EMP;

Example 2:

SELECT e.EMPNO, e.ENAME, e.DEPTNO, e.SAL, 
dense_rank() OVER (partition by e.DEPTNO ORDER BY e.SAL desc) as emp_rank 
FROM EMP e;