SQL Server: Finding Nth Highest Salary

This is one of the interesting SQL queries I came across when I was asked to find the 4th highest salary. Using a sub query, we can first get the top 4 salaries and then find the 4th highest value from them. It is a simple example of how sub queries can be used to solve common SQL problems. 

 

#Select 4th Highest Salary – Method 1

SELECT TOP 1 E_ID
FROM
(
    SELECT DISTINCT TOP 4 E_ID
    FROM EMP
    ORDER BY E_ID DESC
) A
ORDER BY E_ID ASC;

#Select 4th Highest Salary – Method 2

SELECT MIN(E_ID)
FROM
(
SELECT TOP 4 E_ID
FROM EMP
ORDER BY E_ID DESC
) E; 

Description

This is one of the interesting SQL queries I came across when I was asked to find the 4th highest salary.
There are different ways to solve this problem, but using a sub query makes the logic simple and easy to understand.
In the first approach, we first get the top 4 salaries in descending order and then select the lowest value from those 4.
The second approach uses the same idea but uses `MIN()` to get the 4th highest value.
These types of SQL questions are simple to understand but are quite useful for practicing sub queries and sorting.