Get nth highest record from table


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 :

1

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 :

2

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 :

result

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.

Advertisements

About Jasminder

.Net developer and blogger by profession, keen to learn new technologies, love nature, music and cricket.
This entry was posted in Interview Questions, SQL Server and tagged . Bookmark the permalink.

I have a suggestion..

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s