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:

/****** Object:  Table [dbo].[CsharpEmployee]    Script Date: 08/28/2015 06:37:13 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[CsharpEmployee](
    [ID] [int] NOT NULL,
    [Name] [varchar](50) NULL,
    [Salary] [int] NULL
) ON [PRIMARY]

GO

SET ANSI_PADDING OFF
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.

INSERT INTO [Employee].[dbo].[CsharpEmployee]
           ([ID]
           ,[Name]
           ,[Salary])
     VALUES
           (<ID, int,>
           ,<Name, varchar(50),>
           ,<Salary, int,>)
GO

For example:

INSERT INTO [Employee].[dbo].[CsharpEmployee]
           ([ID]
           ,[Name]
           ,[Salary])
     VALUES
           (10
           ,'Akshit'
           ,5000)
GO

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.

Create function GetSalary(@Id int)
Returns decimal
AS
Begin
declare @salary decimal
select @salary =   salary from
(Select Salary,Row_Number() over (order by salary desc) as Counter from    CsharpEmployee group by salary) as t1
 where t1.counter = @id

 RETURN @Salary
 End

select dbo.GetSalary(3) as Salary

Output



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.

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:

SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS rownumber,Salary FROM CsharpEmployee

The final Output will be as in the following image:



Approach 3

This is the simplest and easiest to remember always.

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

Output



Approach 4

This is the simplest and easiest to remember.

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



If you want to refine more on the preceding output, then kindly use the distinct clause as in the following query.

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



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



All queries at one place:

1. 
   

   Create function GetSalary(@Id int)
   Returns decimal
   AS
   Begin
   declare @salary decimal
   select @salary =   salary from
   (Select Salary,Row_Number() over (order by salary desc) as Counter from CsharpEmployee group by salary) as t1
    where t1.counter = @id
   
    RETURN @Salary
    End
   
   select dbo.GetSalary(3) as Salary

2. 
   

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

3. 
   

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

4. 
   

   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

5. 
   

   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.