DENSE_RANK

DENSE_RANK is an Analytical function as well as Aggregate function in Oracle. It returns the rank of the row within the group and it is dense.


Syntax (Aggregate function):


dense_rank(expression1,expression2,…) WITHIN GROUP (ORDER BY expression1,expression2…)
eg


1. Find the salary ranks for all employees.


select salary,dense_rank() over(order by salary asc) as sal_rank from emp;


2.Find the highest salary holder in each department.


select * from
(
select empno,salary,
dense_rank() over(partition by deptno order by salary desc) as sal_rank from emp
)
where sal_rank = 1;


EMPNO SALARY