PL/SQL Dense_Rank

Oracle PL/SQL Dense_Rank Function

The Dense_Rank function allow you to rank items in a group and not like Rank function the Dense_Rank function leaves no gaps in ranking sequence when there are ties.

The Dense_Rank Function syntax:

DENSE_RANK ( ) OVER ( [query_partition_clause] order_by_clause )

Aggregate Function Example:

Select dense_rank(1200, 500) WITHIN GROUP (ORDER BY e.sal, e.comm) 
From emp e ;

Analytic Function Example:

Select e.ename, e.sal, 
dense_rank() OVER (PARTITION BY e.deptno ORDER BY e.sal) 
From emp e
Where e.job='CLERK' ;