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

Awesome…..

Gud one.Keep it up.its very useful…

Thanks

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

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;

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

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

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.

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;