Tuesday, November 20

Nth Highest Salary in SQL

"Find 3rd highest salary" this question was asked to me in some interviews as a fresher. This can be done in two simple ways, both logic i have explained below.

To get  2nd Highest salary, 3rd Highest salary, 4th Highest salary Or Nth topmost salary from an Employee table.

The query is for getting 4th highest salary from Employee table ,

SELECT TOP 1 salary FROM 
SELECT DISTINCT TOP 4 salary FROM employee ORDER BY salary DESC 
ORDER BY salary

In the sub-query, it will select Top 4 distinct highest salary in descending order from the table, then the outer query will select top 1 salary in ascending order, displaying 4th highest salary. The same query can also be written like this:

SELECT MIN ( salary ) FROM Employee WHERE salary 
IN ( SELECT TOP 4 salary FROM Employee ORDER BY salary DESC )

Now you can change and use it for getting Nth highest salary from Employee table as follows

 SELECT TOP 1 salary FROM 

SELECT DISTINCT TOP N salary FROM employee ORDER BY salary DESC )
 ORDER BY salary


**where n > 1 (n is always greater than one)


Or

SELECT MIN ( salary ) FROM Employee WHERE salary 
IN
SELECT TOP N  salary FROM Employee ORDER BY salary DESC )

Note: We use distinct keyword, Because many employees can have same salary.