SQL Interview Question: Find nth highest salary

Solution to finding the 2nd highest salary in SQL
SELECT MAX(Salary) FROM Employee

WHERE Salary NOT IN (SELECT MAX(Salary) FROM Employee )

select MAX(Salary) from Employee

WHERE Salary <> (select MAX(Salary) from Employee )

How would you write a SQL query to find the Nth highest salary? 

Find the nth highest salary using the TOP keyword in SQL Server
SELECT TOP 1 Salary
FROM (
      SELECT DISTINCT TOP N Salary
      FROM Employee
      ORDER BY Salary DESC
      ) AS Emp

ORDER BY Salary

Solution: Not efficient
SELECT * /*This is the outer query part */
FROM Employee Emp1
WHERE (N-1) = ( /* Subquery starts here */
SELECT COUNT(DISTINCT(Emp2.Salary))
FROM Employee Emp2
WHERE Emp2.Salary > Emp1.Salary)

How does the query above work?
The subquery is a correlated subquery
The reason it is called a correlated subquery is because the the subquery uses a value from the outer query in it’s WHERE clause.
A normal subquery can be run independently of the outer query, but a correlated subquery can NOT be run independently of the outer query. 

The subquery is evaluated each and every time a row is processed by the outer query. In other words, the inner query can not be processed independently of the outer query since the inner query uses the Emp1 value as well.


In order to find the Nth highest salary, we just find the salary that has exactly N-1 salaries greater than itself. And that is exactly what the query above accomplishes – it simply finds the salary that has N-1 salaries greater than itself and returns that value as the answer.

Find the nth highest salary in SQL without a subquery
The solution we gave above actually does not do well from a performance standpoint. 

Find the nth highest salary in MySQL
SELECT Salary FROM Employee 
ORDER BY Salary DESC LIMIT n-1,1
The LIMIT clause takes two arguments in that query – the first argument specifies the offset of the first row to return, and the second specifies the maximum number of rows to return.

Find the nth highest salary in SQL Server
SELECT Salary FROM Employee 
ORDER BY Salary DESC OFFSET N-1 ROW(S) 
FETCH FIRST ROW ONLY

Find the nth highest salary in Oracle using rownum
select * from (
  select Emp.*, 
row_number() over (order by Salary DESC) rownumb 
from Employee Emp
)
where rownumb = n;  /*n is nth highest salary*/

Find the nth highest salary in Oracle using RANK
select * FROM (
select EmployeeID, Salary
,rank() over (order by Salary DESC) ranking
from Employee
)

WHERE ranking = N;

Read full article from Find nth highest salary - SQL