Find Nth Highest Salary of Employee – SQL

This question is quite a popular question and it is interesting that I have been receiving this question every other day. I have already answer this question here. “How to find Nth Highest Salary of Employee”.

Suppose that you are given the following simple database table called Employee that has 2 columns named Employee ID and Salary:

Employee

Employee ID	Salary
3	250
4	700
7	400

Now, here is what the SQL will look like:

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

Running the SQL above would return us “400″, which is of course the 2nd highest salary in the Employee table. 

An explanation of the solution

The SQL above first finds the highest salary value in the Employee table using “(select MAX(Salary) from Employee)”. Then, adding the “WHERE Salary NOT IN” in front basically creates a new set of Salary values that does not include the highest Salary value. For instance, if the highest salary in the Employee table is 200,000 then that value will be excluded from the results using the “NOT IN” operator, and all values except for 200,000 will be retained in the results.

This now means that the highest value in this new set will actually be the 2nd highest value in the Employee table. So, we then select the max Salary from the new result set, and that gives us 2nd highest Salary in the Employee table. That’s pretty interesting right?

An alternative solution using the not equals SQL operator

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?

What we did above was find the 2nd highest Salary value in the Employee table. But, what if we want to find the Nth highest salary, where N can be any number whether it’s the 3rd highest, 4th highest, 5th highest, 10th highest, etc? This is also an interesting question – try to come up with an answer yourself before reading the one below to see what you come up with.

The answer and explanation to finding the nth highest salary

SELECT * FROM Employee Emp1 WHERE (N-1) = (SELECT COUNT(DISTINCT(Emp2.Salary))

FROM Employee Emp2 WHERE Emp2.Salary > Emp1.Salary)

How does the query above work?

The query above can be quite confusing if you have not seen anything like it before – the inner query is what’s called a correlated sub-query because the inner query (the subquery) uses a value from the outer query (in this case the Emp1 table) in it’s WHERE clause. 

The most important thing to understand in the query above is that the sub-query 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.