3
Reply

How to select the N<sup>th</sup> maximum salary from Table Emp in Oracle SQL Plus ?

Deepak Verma

Deepak Verma

13y
3.4k
0
Reply

    a nice set based solution. replace the number '7' with whichever N is of interest

    select salary from Employees group by salary
    having salary =
    (select MIN(top_salary.list) from
        (select top 7 distinct_salaries.s as list from
            (select distinct salary as s from Employees
                group by salary) distinct_salaries
        order by distinct_salaries.s desc) top_salary
    )

    select * from emp

    where sal=(select max(sal) from emp

    where level=&level connect by prior sal>sal group bu level)

    SELECT Salary FROM
    (
    SELECT ROWNUM AS SN, Salary FROM
    (
    SELECT DISTINCT Salary FROM Emp ORDER BY Salary DESC
    )
    )
    WHERE SN = N;