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 2^nd 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 2^nd 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 2^nd 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:

[ Copy to Clipboard ] | [ View Source ]

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:

[ Copy to Clipboard ] | [ View Source ]

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.)

[ Copy to Clipboard ] | [ View Source ]

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 2^nd highest salary in the organization are:

[ Copy to Clipboard ] | [ View Source ]

select * from (
                    select DENSE_RANK() OVER(order by salary desc) as RankID, * from dbo.Employee ) InnQ
            where InnQ.RankID = 2

[ Copy to Clipboard ] | [ View Source ]

select * from dbo.Employee where salary = 
 (
  SELECT max(salary) FROM dbo.Employee WHERE salary < 
  (SELECT max(salary) FROM dbo.Employee)
 )

[ Copy to Clipboard ] | [ View Source ]

select * from dbo.Employee where salary = 
 ( 
  Select max(salary) from dbo.Employee where salary NOT IN 
  (Select max(salary) from dbo.Employee)
 )

This article has been taken from SQL Lion, one of the best place for SQL
Source: http://www.sqllion.com/2010/07/nth-highest-salary-query/comment-page-1/#comment-1792