Nth highest salary from table

SQL> select * from emp order by salary desc;

EMPNAME                  SALARY
-------------------- ----------
ra                       566565
su                        89899
fg                        89899
am                        34343
dk                         1000
hjh                        1000

6 rows selected.

Now I want to find the Nth highest salary.


Query 1:

2nd highest: give n =2

SELECT empname, salary
FROM emp e1
WHERE '&N'-1 = (SELECT COUNT(DISTINCT salary) FROM emp e2
WHERE e2.salary > e1.salary);

EMPNAME                  SALARY
-------------------- ----------
su                        89899
fg                        89899

for 1st highest salary: N=1 

SQL> /
Enter value for n: 1
old   3: WHERE '&N'-1 = (SELECT COUNT(DISTINCT salary) FROM emp e2
new   3: WHERE '1'-1 = (SELECT COUNT(DISTINCT salary) FROM emp e2

EMPNAME                  SALARY
-------------------- ----------
ra                       566565

for better explanation,please follow below link

http://www.sqlteam.com/article/find-nth-maximum-value-in-sql-server


 Query2: 

select * from (select empname,salary,dense_rank() over(order by salary desc) as rk from emp) where rk ='&h';

1st highest salary
 
 SQL> select * from (select empname,salary,dense_rank() over(order by salary desc
) as rk from emp) where rk ='&n';
Enter value for n: 1
old   1: select * from (select empname,salary,dense_rank() over(order by salary
desc) as rk from emp) where rk ='&n'
new   1: select * from (select empname,salary,dense_rank() over(order by salary
desc) as rk from emp) where rk ='1'

EMPNAME                  SALARY         RK
-------------------- ---------- ----------
ra                       566565          1

2nd highest salary

SQL> /
Enter value for n: 2
old   1: select * from (select empname,salary,dense_rank() over(order by salary
desc) as rk from emp) where rk ='&n'
new   1: select * from (select empname,salary,dense_rank() over(order by salary
desc) as rk from emp) where rk ='2'

EMPNAME                  SALARY         RK
-------------------- ---------- ----------
su                        89899          2
fg                        89899          2

SQL>