How to find Nth highest salary in Sql Server ?

How to find Nth highest salary in Sql Server ?

Finding the first or second or Nth highest salary of the person is a most important thing for the developers, as a developer we should know this. Mostly we hear this question in the interview.

Consider the below table with table name Employee

SELECT * FROM [dbo].[Employee]

How to find highest salary in Sql Server?

There are so many ways to find highest salary, you can see below some of the examples

Method 1

select max(salary) as highest_salary from [dbo].[Employee]

Method 2

select top 1 salary as highest_salary from [dbo].[Employee] order by salary desc

Method 3

;WITH CTE AS
(
SELECT salary ,ROW_NUMBER() OVER (ORDER BY Salary Desc) AS [Rank]
FROM [dbo].[Employee] group by salary
)
SELECT  salary as highest_salary
FROM CTE
WHERE [Rank]=1;

How to 2nd highest salary in Sql Server?

We are seen some of the methods to find highest salary above, we cannot use method 1 and method 2 to find the 2nd highest salary, but we can use the method 1 by little change in the code.

There are so many ways to 2nd highest salary, you can see below some of the example

Method 1

SELECT MAX(salary) FROM Employee WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee);

Method 2

SELECT TOP 1 Salary FROM ( SELECT TOP 2 Salary FROM Employee ORDER BY Salary DESC) AS MyTable ORDER BY Salary ASC;

Method 3

;WITH CTE AS
(
SELECT salary ,ROW_NUMBER() OVER (ORDER BY Salary Desc) AS [Rank]
FROM [dbo].[Employee] group by salary
)
SELECT  salary as highest_salary FROM CTE WHERE [Rank]=2;

How to Nth highest salary in Sql Server?

Previously we have seen highest salary and 2nd highest salary solutions, also we know the query using CTE is used again used in 2nd highest salary with a little modification, the modification is we are changing the rank

If we want 1st highest salary we have to give rank as 1

If we want 2nd highest salary we have to give rank as 2

So we can use the same query to find the nth salary like below

;WITH CTE AS
(
SELECT salary ,ROW_NUMBER() OVER (ORDER BY Salary Desc) AS [Rank]
FROM [dbo].[Employee] group by salary
)

SELECT  salary as highest_salary FROM CTE WHERE [Rank]=”Nth”;

Example

;WITH CTE AS
(
SELECT salary ,ROW_NUMBER() OVER (ORDER BY Salary Desc) AS [Rank] 
FROM [dbo].[Employee] group by salary
)
SELECT  salary as highest_salary FROM CTE WHERE [Rank]=”Nth”;

Finding 5th highest salary in sql server

;WITH CTE AS
(
SELECT salary ,ROW_NUMBER() OVER (ORDER BY Salary Desc) AS [Rank]
FROM [dbo].[Employee] group by salary
)
SELECT  salary as highest_salary
FROM CTE WHERE [Rank]=5;

Leave a Reply

%d bloggers like this: