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

About sqlandplsql

To learn more about the Oracle, SQL, PL SQL, Performance Tuning, Database Modeling, Ubuntu, MySQL etc .Suggestions, comments, feedbacks and referrals are highly appreciated.
This entry was posted in Oracle and tagged , , , , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s