# 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

## 10 thoughts on “Nth highest salary”

1. Ela.Nanthini says:

Gud one.Keep it up.its very useful…

1. mayur gound says:

select salary from employee e where (n-1)=(select count(distinct(salary)) from employee e1 where e1.salary>e.salary)

2. Saurabh says:

select * from (select empno, salary,rownum rk from emp order by salary desc) where rk = 2; This will NOT give you second highest salary. It will give you second lowest salary.
For second highest salary, use below SQL:
select * from (select name, salary, rownum rank from employee order by salary desc) where rank = 2;

1. Both the query mentioned in your comment are same (both use order by salary desc).

1. Saurabh says:

Sorry for typo..
here is the right query:

select * from (select name, salary, rownum rank from emp order by salary desc) where rank ((select count(1) from emp)-1 );

3. Utsav Shubham says:

Guys!!
“select * from (select empno, salary,rownum rk from emp order by salary desc) where rk = 2;”
this will neither give you the second highest salary nor the second lowest.
It will fetch the second latest record inserted.
I mean if you use rk =1 in the where clause then it will fetch you the latest record inserted.

1. Utsav Shubham says:

I guess use of ROW_NUMBER() function is really a good idea but if at all ‘rownum’ is your favorite then you can use
SELECT *
FROM (select salary.* ,rownum rk from
(select * from emp ORDER BY salary DESC) salary
where rownum <= 2 )
WHERE rk = 2;