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:

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.


For example:


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.
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.
If I pick only an inner query and execute it in SQL Server then I’d get the result as in the following image:


The final Output will be as in the following image:



Approach 3

This is the simplest and easiest to remember always.
Output



Approach 4

This is the simplest and easiest to remember.
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.
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. 
   
   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
2. 
   
   1. SELECT * FROM (SELECT DENSE_RANK() OVER (ORDER BY SALARY DESC) AS rownumber,Salary FROM CsharpEmployee) AS Maxs WHERE rownumber = 3
3. 
   
   1. select top 1 salary from (select distinct top 3 salary from CsharpEmployee order by salary desc) as t1 order by salary asc
4. 
   
   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
5. 
   
   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.