Oracle SQL - Nth highest salary

Below are the queries for finding nth highest salary of employees:

Using correlated subquery:

SELECT e1.salary,e1.*

  FROM employees e1

 WHERE (n-1) = (SELECT COUNT(DISTINCT(e2.Salary))

                FROM employees e2

               WHERE e2.Salary > e1.salary);

Note : where n is the number of the highest salaried employee

  
Using dense_rank() analytics function;   

 

SELECT *

  FROM (SELECT salary, dense_rank() OVER(ORDER BY salary DESC) AS ranking

          FROM employees) emp

 WHERE emp.ranking = n;


 Note : where n is the number of the highest salaried employee

Examples:

Below query shows the employees salaries in descending order.

Below query shows  the 2nd highest salaried employee using correlatd subquery.

Below query shows the 1st highest paid salary