Find Out The Top Nth Salary

Find Out The Top Nth Salary

Method #1

SELECT last_name, job_id, salary

  FROM employees

ORDER BY salary DESC

FETCH NEXT 5 ROWS ONLY;

Method #2

SELECT last_name, job_id, salary

  FROM (SELECT ROWNUM AS RANK,

               last_name,

               job_id,

               salary

          FROM (  SELECT *

                    FROM employees

                ORDER BY salary DESC))

 WHERE RANK < 6;

Method #3

SELECT *

  FROM (SELECT last_name,

               job_id,

               salary,

               DENSE_RANK () OVER (ORDER BY salary DESC) ranking

          FROM employees)

 WHERE ranking < 6                            -- Replace 6 with any value of N

Method #4

SELECT *

  FROM (SELECT last_name,

               job_id,

               salary,

               ROW_NUMBER () OVER (ORDER BY salary DESC) AS rn

          FROM employees)

 WHERE rn < 6

Method #5: 2nd Higest Salary

  SELECT *

    FROM Employees E1

   WHERE (2 - 1) = (SELECT COUNT (DISTINCT (E2.Salary))

                      FROM Employees E2

                     WHERE E2.Salary > E1.Salary)

ORDER BY E1.Salary DESC