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