How to find nth highest salary

How to find nth highest salary?

At the time of interview a very common question that is asked to almost all the candidates is to find the 2nd highest salary. And very often too candidates are unable to answer it. To find highest salary is not a big deal. But to find second highest salary... need to think a bit. But again, not a difficult task.

Reference Tables:Salary

Create Table Script for Salary table is as:




CREATE TABLE Salary(

SalaryId INT IDENTITY(1,1) PRIMARY KEY

, EmployeeId INT

, Salary NUMERIC(22,2)

)







Script for inserting the data into Salary table:




INSERT INTO Salary

VALUES(1,1,7000.00),

      (2,2,8000.00),

      (3,3,10000.00),

      (4,4,5000.00),

      (5,5,25000.00)

Getting the Highest Salary

For Highest salary we simply use the following SQL Query:

select MAX(Salary) from Salary

The output of the above SQL Query will be 25000.00

Getting the Second Highest Salary

But, what about the second highest salary? In that case we need to modify the above SQL Query. What we need to do is to select the maximum Salary from the Salary table where Salary is greater than maximum Salary from the Salary table. So the modified SQL Query will be like:

select MAX(Salary) from Salary where Salary< (select MAX(Salary) from Salary)

The output will be 10000.00

So getting the second highest salary is simple enough.

Getting the Nth Highest Salary

What if we want to get the nth highest salary? Seems a bit complicated! Is'nt it? So let us try to get the nth highest salary in SQL. Again we will have to modify the SQL Query. So let us do it.

First we need to get the TOP N salary in descending order, where N is the nth highest salary. This can be easily done using TOP N and ORDER BY Clause with DESC. The SQL Query is;

SELECT DISTINCT TOP 2 Salary from Salary ORDER BY Salary DESC

This will give the top 2 salaries arranged in Descending order. We will save the output of this SQL query in an alias Result and from this Result we will fetch the top 1 salary simultaneously arranging the salary in ascending order. What we will get is second highest salary. i.e, 10000.00

The SQL Query look like this:

SELECT TOP 1 Salary FROM 

(

SELECT DISTINCT TOP (2)Salary 

FROM Salary

ORDER BY Salary DESC

) Result

ORDER BY Salary

But, the question was to get the nth highest salary. No issues! We are done with it. Just replace 2 in the inner query with 3 to get the 3rd highest salary, with 4 to get the 4th highest salary and so on.

CONCLUSION

So, we have seen step-by-step how to get the highest, second highest, and nth highest salary in SQL. All these tasks are achieved using normal SQL Query and Nested Query. The same task can also be achieved using CTE i.e., Common Table Expression.

FEEDBACK

Feel free to comment in case there is any doubt in above queries. You can also post new questions in the comment section.