Find nth highest salary SQL query

By | April 25, 2014

Preparing for an interview and have very less time left to cover all the topics. Don’t worry here we are giving you a collection of interview questions, which we have chosen after doing a very thorough research with hundreds of candidates .First thing is in most of the interviews you will encounter the SQL query as it is. In other some modified form of these but you will able to answer those , provided you have read our posts carefully.

1.Given an Employee table shown below .Write a SQL query to Find the second highest salary of an employee.

The SQL query to find highest salary is pretty easy and we can directly use SQL aggregate function MAX.
Here’s the query to find highest salary.

How to find the second highest salary ?
Remember that SQL is based on sets.SO what we can do is, consider two sets SET1 {set of all the salaries in the employee table} and SET2{ single element highest salary in Employee table}.
so, SET1={500,1000,50,200} ,SET2={1000} .
Now we just have to select the highest element form SET1 which is not in SET2. The SQL query for that is ;

If you somehow managed to answer this question you will be asked to answer this question :

2.Write a SQL query to find the Nth highest salary from Employee table

I you will think carefully the logical solution is something like this .
while(true)
{
Select a salary from Employees table and count the number of salaries greater than it if count ==N-1 than return it.
}

We can easily write a nested SQL query for this using the count aggregate function .Here’s the solution

It’s just one of the solutions I request readers to think about other possible solutions.

Leave a Reply

Your email address will not be published. Required fields are marked *