Oracle DENSE_RANK function

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

 

Advertisements

Oracle RANK function

RANK is an Analytical function as well as Aggregate function in Oracle. It returns the rank of the row within the group.

Syntax (Aggregate function):

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 rank(7700,18) WITHIN GROUP (ORDER BY salary,empno) as rk from emp;

RK
———-
7

Syntax (Analytical function):

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,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            6
7700            7
7800            8
8500            9
8500            9
9000           11
9500           12
10000         13
10000         13
12000         15

Note that rank function will assign same number if there is a tie and it will skip those many numbers.

From the above example there are two employees have salary of 7500 and rank assigned in 4 and next rank is 6. So it skipped rank number 5. You can use DENSE_RANK() function for without skipping numbers.

Read DENSE_RANK() function also

2. Find the highest salary holder in each department.

select * from
(
select empno,salary,
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