how can i retrieve the 2nd highest salary from emp table (oracle)

To get the dynamic salary(for example 2nd sal, 3rd sal like that) ---------------------------------------------------------------- select * from emp a where &n=(select * from emp b where a.sal < b.sal) or select max(sal) from emp where sal>(select max(sal) from emp)

Here are two queries to get the Nth highest salary:

1. This query gives you all the columns of emp table

SELECT * FROM (SELECT f.*,dense_rank() OVER(ORDER BY SALARY DESC) rn FROM Emp f) WHERE rn=&n

2. This query gives you the salary column data only


SELECT MIN(SALARY) FROM EMP f WHERE &n > (SELECT COUNT(DISTINCT SALARY) FROM emp where salary > f.salary)

The Advantage of using a dense_rank is that in case if there are more than 1 employee having a same salary at Nth position then both the employee details will be listed

Hi,

this is not working in oracle.
can u please tell us how to handle it in oracle(2nd high sal)