Nth highest salary

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