Get Nth highest salary from a table

U can use below queries to get nth highest sal from a table.

method 1 :


select * from (select distinct sal from scott.EMP order by sal desc) where rownum<=&n
minus
select * from (select distinct sal from scott.EMP order by sal desc) where rownum<=&n-1;

method 2:

SELECT DISTINCT sal
           FROM scott.EMP a
          WHERE &n - 1 = (SELECT COUNT (DISTINCT (sal))
                            FROM scott.EMP b
                           WHERE a.sal < b.sal);
                         

method 3 :

SELECT DISTINCT sal
           FROM scott.EMP a
          WHERE &n  = (SELECT COUNT (DISTINCT (sal))
                            FROM scott.EMP b
                           WHERE a.sal <= b.sal);
                           

the way the queries 2 and 3 works is by finding the number of salaries that are greater than the current salary being searched for. If that number is exactly equal to N-1, then we know that we have the Nth highest salary, otherwise we continue searching through the table. If this description does not make sense, then read on for a more detailed description, which should make sense to you.

if u want detailed info on this go through below link

http://www.programmerinterview.com/index.php/database-sql/find-nth-highest-salary-sql/

method 4:


select level, max(sal) from scott.emp
where level = &n
connect by prior sal > sal
group by level;

select level, max(sal) from scott.emp
where level=5
connect by prior sal > sal
group by level