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…)
Example :- Find the rank of the employee whose employee number is 18 and salary is 7700
select dense_rank(7700,18) WITHIN GROUP (ORDER BY salary,empno) as rk from emp;
RK
———-
7
Syntax (Analytical function):
dense_rank() over( [partition by column] order by column )
Examples:-
Consider below EMP table structure
| EMPNNO | EMPNAME | SALARY | DEPTNO |
| 11 | Solomon | 10000 | 5 |
| 12 | Susan | 10000 | 5 |
| 13 | Wendy | 9000 | 1 |
| 14 | Benjamin | 7500 | 1 |
| 15 | Tom | 7600 | 1 |
| 16 | Henry | 8500 | 2 |
| 17 | Robert | 9500 | 2 |
| 18 | Paul | 7700 | 2 |
| 19 | Dora | 8500 | 3 |
| 20 | Samuel | 6900 | 3 |
| 21 | Mary | 7500 | 3 |
| 22 | Daniel | 6500 | 4 |
| 23 | Ricardo | 7800 | 4 |
| 24 | Mark | 7200 | 4 |
1. Find the salary ranks for all employees.
select salary,dense_rank() over(order by salary asc) as sal_rank from emp;
SALARY SAL_RANK
———- ———-
6500 1
6900 2
7200 3
7500 4
7500 4
7600 5
7700 6
7800 7
8500 8
8500 8
9000 9
9500 10
10000 11
10000 11
12000 12
12000 15
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 SAL_RANK
———- ———- ———-
13 9000 1
17 9500 1
19 8500 1
23 7800 1
10 12000 1
3. Nth highest salary
Query using the Dense_rank() is the one of the efficient way to find Nth highest salary.
select * from
(
select empno,salary,
dense_rank() over(order by salary desc) as rk from emp
)
where rk = 1;
EMPNO SALARY RK
———- ———- ———-
10 12000 1
Instead of 1 if you substitute 2 it will provide 2nd highest salary/salaries
select * from
(
select empno,salary,dense_rank() over(order by salary desc) as rk from emp
)
where rk = 2;
EMPNO SALARY RK
———- ———- ———-
11 10000 2
12 10000 2
Read RANK function also
