Nth highest salary

Published on August 17 2015 by LakshmiSaahul,Dhana Royal

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) )

Advertising