This is one of the most common questions asked for database related questions in interviews. So i decided to write a post on this one so that it can help others also. There are 2 main possible solutions that I will use here. One of them is using a simple sub-query and the other one uses the concept of the co-related sub query.
1. So let’s start by creating a simple table EmpSalary with two columns of Id & Salary and add some records into it.
2. Next simply view the records Select * from EmpSalary. It will display the results as below :
3. So let’s start to try and get the solutions for this. For our purpose, we will be searching for the 3rd highest salary in the table i.e. the record with id 3 and salary 650.
Solution 1 – Simple sub-query solution :
With this approach, first we will fetch records with the number equal to n in descending order of the salary i.e. we will fetch top 3 records with order by descending on the Salary column. For this we use the following query :
SELECT TOP 3 * FROM EmpSalary ORDER BY Salary DESC
This will give us the required result as the last record in the result set. Next we will apply an outer query on this query to get the record with the minimum salary out of the above fetched results. So our final query & results become :
So this was the first way in which first the inner sub-query fires and then finally the outer query fires to get the final results.
Solution 2 – Using co-related sub-query :
Before we start with this solution, let me give you an idea of the co-related sub-query. It is different from a simple sub-query.
Sub-query vs Co-related sub-query :
In a simple sub-query, first the sub-query or the inner query is fired to get the results. Then the outer query is fired on the results of the inner query to get the final results. The sub-query can work independently of the main query.
On the other hand, in co-related sub-query, the inner query is dependent on the outer query to generate any results for the final results. As a result, the sub-query is executed for each record which an outer query generates. So the inner query is dependent on the result of the outer query to generate the final result set.
So let’s start with the second solution. this will then give you more idea of the co-related sub-query.
To start with, the logic to get the results is, to get the number of records which are available in the record set and are greater then the result required i.e. to get the 3rd highest record, we will try to get the 2 records which are greater then the 3rd highest record (which we are attempting to fetch) in the overall result set. To do this, we will use the co-related sub-query which will be as below :
Here, points to notice are
1. We are using aliases for the same table and the inner query is the one which will be fetching those records which are having salary as greater then the record being searched.
2. Condition N-1, which will get the results one less then the index being searched i.e. it will get the 2 records which are greater then our third or searched record.
3. Finally the inner query uses the results of the outer query i.e. it is the co-related sub-query. So, for each record from the outer query, the inner query is fired which is nothing but the co-related sub-query.
Here, you can replace N with the nth record you want to select i.e the 3rd , 4th or the 5th highest record in the table. The outer query starts executing and for each record of the outer query, corresponding inner query is also executed. When the query reaches the nth required record in the table, the where condition of this query is satisfied as the where condition becomes equal to the count(n-1) of the records that are having their values greater then the record being searched, and the result record is selected.
So from the above two solutions, i feel the first one is better as it does not queries again and again like the co-related sub-query do for each result of the outer query.
As always, any other solution/feedback is welcome.