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

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.

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:

final Output

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

run

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

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

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
10. RETURN @Salary
11. End
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.

8 Comments

Join the conversation! Your thoughts help the community grow.

Santhakumar Munuswamy11y ago
Good article. Thanks for sharing
Gopi Chand11y ago
Great
Gowtham K11y ago
Goo one
Pankaj Kumar Choudhary11y ago
Nice Explain Sir........
Rakesh11y ago
Good one
Karthikeyan K11y ago
Good one sir..Thanks for share
Yashwant Vishwakarma11y ago
awesome one :)
Mohammed Ibrahim11y ago
nice