Nth Highest Salary Query
Problem: Show the list of all employees from the employee table having second highest Salary or Nth highest salary in the organization.
Solution: Well we can achieve the result by so many ways but in this post I would like to do it by using co-related queries. Co-related queries are very interesting stuff as it gives you option to process data row wise. But at the same time it has a big drawback. Row wise processing causes bottle neck for the performance of the query.
Query to get the list of employees getting 2nd highest salary (Set the value of @SalaryPosition to get Nth highest salary):
DECLARE @SalaryPosition int SET @SalaryPosition = 2 SELECT * FROM Employee E1 WHERE @SalaryPosition = ( SELECT COUNT(DISTINCT E2.Salary) FROM Employee E2 WHERE E2.Salary >= E1.Salary )
Well the query looks simple but understanding it is little bit complex. Let’s explore the process behind it.
I have taken the variable @SalaryPosition in order to make it standardize while checking salaries in more than one level.
Before going further, let me tell you the concept behind the co-related query.
Co-Related Query: In short, it’s a type of nested sub query where the inner query is referenced to some value of the outer query. And the inner query is executed for each record in the outer query making it slowL. More appropriate example of a Co-Related query is the IN () operator.
In our query, to get the 2nd highest salary, works in the same way too. Let’s dive a little deeper.
Sample data used in employee table:
| slNo | empname | salary |
|
101 |
Ram |
5000 |
|
102 |
Abhishek |
7500 |
|
101 |
kumar |
5000 |
|
104 |
prasad |
6570 |
|
102 |
Jumla |
7500 |
|
101 |
Harkesh |
12000 |
|
101 |
John |
4000 |
Run the below query to create the table in your database and to insert data into it.
CREATE TABLE [dbo].[employee]( [slNo] [int] NULL, [empname] [varchar](50) NULL, [salary] [float] NULL ) INSERT INTO [dbo].[employee]([slNo],[empname],[salary]) select '101', 'Ram', '5000' Union All select '102', 'Abhishek', '7500' Union All select '101', 'kumar', '5000' Union All select '104', 'prasad', '6570' Union All select '102', 'Jumla', '7500' Union All select '101', 'Harkesh', '12000' Union All select '101', 'John', '4000'
So we have taken two aliases for the employee table i.e. E1 and E2.
Now let’s see how to get the employee names getting 2nd highest salary.
Query:
DECLARE @SalaryPosition int SET @SalaryPosition = 2 SELECT * FROM Employee E1 WHERE @SalaryPosition = ( SELECT COUNT(DISTINCT E2.Salary) FROM Employee E2 WHERE E2.Salary >= E1.Salary )
Here E1 is the outer query and E2 is the inner query.
Corollary: Let’s take the first record in the table E1 where the salary is 5000 and assume it as the second highest salary. But how will you know if it is actually the second highest salary or not. If 5000 is the second highest salary, then the distinct count of all the salaries from table E2 which are greater than or equal to 5000 must be 2.
The first record from E1 (salary = 5000) will be compared to all the records in E2 (salary).
| Table E1 (considering the first record only) | Table E2 (distinct salary) | Distinct count where salary is >= 5000 |
|
5000 |
4000 5000 6570 7500 12000 |
4
|
Hence 5000 is not the highest salary as we have 4 more salaries greater than equal to 5000. But it is concluded that 5000 is at the fourth position.
Now let’s take the second salary from table E1 and do the same comparison as in the earlier case.
The second salary is 7500 and as per rule the count of distinct salaries greater than equal to 7500 must be 2.
| Table E1 (considering the second record only) | Table E2 (distinct salary) | Distinct count where salary is >= 5000 |
|
7500 |
4000 5000 6570 7500 12000 |
2
|
Here in this case our condition in the where clause of outer query is satisfying. Let’s dissect the query for this particular case.
Original Query:
DECLARE @SalaryPosition int SET @SalaryPosition = 2 SELECT * FROM Employee E1 WHERE @SalaryPosition = ( SELECT COUNT(DISTINCT E2.Salary) FROM Employee E2 WHERE E2.Salary >= E1.Salary )
Step 1:
SELECT * FROM Employee E1 WHERE 2 = ( SELECT COUNT(DISTINCT E2.Salary) FROM Employee E2 WHERE E2.Salary >= E1.Salary )
Step2: (where the salary is 7500, inner query will return 2 as only two salaries are there in table E2 that are greater than equal to 7500.)
SELECT * FROM Employee E1 WHERE 2 = ( 2 )
Step3: (Now 2 = 2, the outer query condition satisfies, hence it will return the corresponding record in the result set.
Once it returns the record 101, Abhishek, 7500 it will iterate through other records in table E1 to check if any other record is satisfying the same condition. In our example two records satisfies the condition “where 2=2”, hence both the records returned to the result set.
Finally we got all the employee information with second highest salary.
If you want to check for highest salary or second highest salary, then just change the value of @SalaryPosition accordingly.
Other ways to get list of employees getting 2nd highest salary in the organization are:
select * from ( select DENSE_RANK() OVER(order by salary desc) as RankID, * from dbo.Employee ) InnQ where InnQ.RankID = 2
select * from dbo.Employee where salary = ( SELECT max(salary) FROM dbo.Employee WHERE salary < (SELECT max(salary) FROM dbo.Employee) )
select * from dbo.Employee where salary = ( Select max(salary) from dbo.Employee where salary NOT IN (Select max(salary) from dbo.Employee) )
Let us know your valuable feedback on the above methodology.
very well articulated. thanks
Thanks a lot for this article. finally i understood, how co-related queries are working. keep it up boss.
A truly fantastic idea and approach.
I look forward for ur upcoming posts.
Nice post. All very good points.
There are a few sites that I’ve registered, but there are only few who explain any problem so elaborately.
Thanks
What a brilliant post Arun! You should do a series!
very good explanation. Expecting some more posts like this.
Hi Arun,
Excellent Post.Spoon feeding explanation.Please post many articles like this
Arun
FINALLY i got the exact info what I am searching for since many days
Thanks for the detailed explanation
Great Job
Thanks
Sunil
Great post written keeping average SQL user in view!!
Hi Arun,
How simply you explain the concept is excellent… only on a single read everything is clear and its clear the other concept too(depends on individuals.)
.
Thanks for the post and simple explanation.
You proved simplicity is the best…:)
Mahesh Khadse
thanx for explain in step by step..
Hi,
but below query gives all the records from employee table.
i agree with you till nested query. but what after getting the count from nested query. According to you it will be llike below one.
but plz check the output of below query….
SELECT *
FROM Employee E1
WHERE 2 =
(
2
)
Hi,very good post but please check the out put of that
SELECT *
FROM Employee E1
WHERE 2 =
(
2
)
@Mukund and @umesh,
I know it will return all the records, the steps I shown above is the illustration of internal working of co-related query. Please have a look closely on all the steps, by the time it will come to Step 2:, the outer query will have only one record, hence it will return only one record to the final result set.
Thanks,
Arun
The explanation is simple but excellent and even professors cannot explain in such a simplest way. This is what I am looking for in my current project. Thank you…
Regards,
Ranjan Kumar
Hi Arun,
Can you please post an article explaining the Lazy loading process in ORM mapping (Object Relational Mapping) in terms of space-time complexity? I am looking for it to use in our project.
Regards
Ranjan
@Mukund
You can with details that Arun had explained,
Second query will be serching to distincts from the salary.
Superb explanation can u explain joins with examples?
Thanks in advance
pavan
Hi, This helped me alot to understand the concept of how to find Nth highest salary. explainations is very supportive .
Thanks alot Amit !!
Thanks a lot! for nice explanation ! i was searching for it for long time..
a nice explanation.
I was searching for explanation more then 6 months.
thanks..for explaining this step by step…..
thank for well define step by step explaining
Excellent Explanation
Excellent Explanation
Detailed and wellpresented explanation
Hi,
Now,this tricky query very clear,I gave interview in IT Industry around 2 years and this query put up every time but I can’t able to explain him exactly.
Today, I am very very happy to clear concept on this query.
Thank You So Much.
Very Nice Article…Thanks again…
Regards,
Rajesh
This is very crisp and crystal clear.
Thank You so much.
Hi Arun,
I was looking for explanation of this query
. Thanks for nice explanation.
Please give me your email id or tell me any other way so that i can discuss my doubts with you directly (if possible).
Thanks.
good job man. this really helped a lot
thx
U certainly owe one from us… Brilliant is not the word for U.. Thanks John Faruki
Nice Query. Never seen such explanation.
nice article.Thanks
hi..its clear and simple..thanks..
God Job Man. Its a nice way you explain quite simplest complex query..
Super……….& …thanks………
@joestudent
Thanks For Explanation.It’s Very well article.
What an explanation. Very well explained.
Thanks, I was searching for such an article for so long.
Awesome dude………!u r Rocking……!
plz correct that ..in second table that should be >=7500………thank you