Tag: second highest salary

Nth highest salary

~ sqlandplsql ~ 10 Comments

There many ways to find nth highest salary.

 

Consider below EMP table structure

EMPNNO EMPNAME SALARY DEPTNO
10 Bill 12000 5
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

List of all employees,salary,rank in descending order

SALARY EMPNO RANK
12000 10 1
10000 11 2
10000 12 2
9000 13 3
7500 14 4
7600 15 5
8500 16 5
9500 17 6
7700 18 7
8500 19 8
6900 20 9
7500 21 9
6500 22 10
7800 23 11
7200 24 12

1) Find highest salary/salaries using DENSE_RANK() function

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

2) Find 2nd highest salary/salaries using DENSE_RANK() function

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

If you replace 2 with N in above query it will provide Nth highest salary/salaries.

3. Find 2nd highest salary using ROW_NUMBER() function

select * from (select empno, salary, row_number() over(order by salary desc) rk from emp order by salary desc ) where rk = 2;

EMPNO                     SALARY              RK
———-           ———-         ———-
11                                10000                    2

Note that this query will not give multiple records if there are employees with same salaries ( compare query (2) and (3) )

4. Find 2nd highest salary using ROWNUM

select * from (select empno, salary,rownum rk from emp order by salary desc) where rk = 2;

EMPNO                           SALARY          RK
———-           ———-         ———-
11                                        10000               2

Note that this query also will not give multiple records if there are employees with same salaries ( compare query (2) and (4) )

Conclusion :- Use DENSE_RANK, ROW_NUMBER(), ROWNUM with respect to the requirement. As per my experience query with ROW_NUMBER() is most appropriate and good for performance.

You may interested in below posts also
RANK() function
DENSE_RANK() function
Autonomous Transaction
Cursors
Soundex function 
Oracle Interview Questions

Advertisements

Oracle DENSE_RANK function

~ sqlandplsql ~ Leave a comment

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