Post
Ask Question

Various Approaches to Find Nth Salary in SQL Server 2008R2

This article is about understanding various ways to find the nth salary in SQL Server. And I feel it would be a good brain teaser because in a single article you will learn various approaches such as Functions, Dense_Rank(), and Row_Number(), Self-Join, etc.

The requirement is to find the 3rd highest salary in SQL Server: A simple an practical approach.

Let’s start with the solution.

Table Creation

This is the basic need to perform queries. I’ve created a CsharpEmployee table with a few columns such as Id, Name and Salary. We’d be concentrating on the Salary column primarily.

The SQL snippet to create a table is written below:

  1. /****** Object:  Table [dbo].[CsharpEmployee]    Script Date: 08/28/2015 06:37:13 ******/  
  2. SET ANSI_NULLS ON  
  3. GO  
  4.   
  5. SET QUOTED_IDENTIFIER ON  
  6. GO  
  7.   
  8. SET ANSI_PADDING ON  
  9. GO  
  10.   
  11. CREATE TABLE [dbo].[CsharpEmployee](  
  12.     [ID] [int] NOT NULL,  
  13.     [Name] [varchar](50) NULL,  
  14.     [Salary] [int] NULL  
  15. ) ON [PRIMARY]  
  16.   
  17. GO  
  18.   
  19. SET ANSI_PADDING OFF  
  20. GO  
Kindly insert some value using the SQL snippet given below to perform queries. This is very simple and at least for a newbie you would get a chance to do hands-on insert queries as well.
  1. INSERT INTO [Employee].[dbo].[CsharpEmployee]  
  2.            ([ID]  
  3.            ,[Name]  
  4.            ,[Salary])  
  5.      VALUES  
  6.            (<ID, int,>  
  7.            ,<Name, varchar(50),>  
  8.            ,<Salary, int,>)  
  9. GO  
insert value

For example:
  1. INSERT INTO [Employee].[dbo].[CsharpEmployee]  
  2.            ([ID]  
  3.            ,[Name]  
  4.            ,[Salary])  
  5.      VALUES  
  6.            (10  
  7.            ,'Akshit'  
  8.            ,5000)  
  9. GO  
id name

Note: I intentionally wrote salary 7777 two times for ID 3 and 4.

Approach 1

This is one of the interesting ways to get the 3rd highest salary using the Inline Table value function and using this you would also get hands-on how experience creating a Table value function and its utilization.
  1. Create function GetSalary(@Id int)  
  2. Returns decimal  
  3. AS  
  4. Begin  
  5. declare @salary decimal  
  6. select @salary =   salary from   
  7. (Select Salary,Row_Number() over (order by salary desc) as Counter from    CsharpEmployee group by salary) as t1  
  8.  where t1.counter = @id  
  9.    
  10.  RETURN @Salary  
  11.  End  
  12.   
  13. select dbo.GetSalary(3) as Salary  
Output

GetSalary

Note: We’ve used Row_Number in the Salary column and made Group By on the salary column to remove duplicacy. There are two rows having salary 7777 two times for ID 3 and 4.

Approach 2

Another way is to use the Dense_Rank() function. The beauty of this function is, it gives a unique number to each row in spite of the Salary column having the same salary in multiple rows.
  1. SELECT * FROM (SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS rownumber,Salary FROM CsharpEmployee) AS Maxs WHERE rownumber = 3  
If I pick only an inner query and execute it in SQL Server then I’d get the result as in the following image:
  1. SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS rownumber,Salary FROM CsharpEmployee  
get the result

The final Output will be as in the following image:

final Output

Approach 3

This is the simplest and easiest to remember always.
  1. select top 1 salary from (select distinct top 3 salary from CsharpEmployee order by salary desc) as t1 order by salary asc  
Output

run

Approach 4

This is the simplest and easiest to remember.
  1. Select * From CsharpEmployee C1 Where 3 = (Select Count(Distinct(C2.Salary)) From CsharpEmployee C2 Where C2.Salary >= C1.Salary)  
Note: The preceding query is also an example of a Self Join.

Output

Output

If you want to refine more on the preceding output, then kindly use the distinct clause as in the following query.
  1. Select distinct salary from ( Select * From CsharpEmployee C1 Where 3 = (Select Count(Distinct(C2.Salary)) From CsharpEmployee C2 Where C2.Salary >= C1.Salary)) as tempTable  
Output

result

Approach 5

This is another way to find the 3rd highest salary. It is a little complex if you go with more levels like 6th, 7th and so on. The reason is that you need to use a more inner query or sub-query to get the desired result.

select max(salary) from CsharpEmployee where salary < (select max(salary) from CsharpEmployee where salary < (select max(salary) from CsharpEmployee))

Output

Selary

All queries at one place:

    1. Create function GetSalary(@Id int)  
    2. Returns decimal  
    3. AS  
    4. Begin  
    5. declare @salary decimal  
    6. select @salary =   salary from   
    7. (Select Salary,Row_Number() over (order by salary desc) as Counter from CsharpEmployee group by salary) as t1  
    8.  where t1.counter = @id  
    9.    
    10.  RETURN @Salary  
    11.  End  
    12.   
    13. select dbo.GetSalary(3) as Salary  

    1. SELECT * FROM (SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS rownumber,Salary FROM CsharpEmployee) AS Maxs WHERE rownumber = 3   

    1. select top 1 salary from (select distinct top 3 salary from CsharpEmployee order by salary desc) as t1 order by salary asc  

    1. Select distinct salary from ( Select * From CsharpEmployee C1 Where 3 = (Select Count(Distinct(C2.Salary)) From CsharpEmployee C2 Where C2.Salary >= C1.Salary)) as tempTable  

    1. select max(salary) from CsharpEmployee where salary < (select max(salary) from CsharpEmployee where salary < (select max(salary) from CsharpEmployee))  

For sure this is one of the most common interview questions.


Similar Articles