Nth Highest Salary In Sql Server

In SQL Server, the nth highest salary is the query that returns the nth highest salary in a list. It is a useful tool for determining which employees are making the most money in a company.

The query can be used to display only the top ten salaries of an employee who has been working at your company for more than one year, or it can be used to show all employee salaries by salary range.

The syntax for this query is: SELECT TOP 10 [salary] FROM [Employee] WHERE [year_of_service] >= 1 ORDER BY [salary].

Nth Highest Salary In Sql Server

Lets start step by step with different ways. First I will get all employees records from employee table so we will be clear with all employees data.

select * from Employees

SQLCopy

Find max salary using the max function,

select max(Salary) as Salary from Employees;

SQLCopy

Using top key word and subquery

declare @nthHighest int
set @nthHighest = 5 -- This one is parameter for which highest position you want to find Here 5th Highest
select top 1 Salary from
(
select distinct top (@nthHighest) salary as Salary from Employees order by Salary desc
) as result
order by Salary;

SQLCopy

You can find nth position highest salary using above query. You just need to change declared variable (set @nthHighest = 5) according to you requirement. In the above query, I have set it to 5 so it will return 5th highest salary as per below screen where I have marked with red color.

Find 2nd highest salary using sub-query

select top 2 Salary from Employees order by Salary desc

-----------------------------------------------------

Select Max(Salary) as Salary from Employees
where Salary <(select MAX(Salary) from Employees)

SQLCopy

Using common table expression

Declare @Nhighest int
set @Nhighest =3;

WITH RESULT AS
(
    SELECT distinct SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM Employees
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = @Nhighest

SQLCopy

Using common table expression we can find nth highest salary as above query. I have set @Nhighest =3 so it will return 3rd highest salary from employees table.

Declare @Nhighest int
set @Nhighest = 5;

WITH RESULT AS
(
    SELECT distinct SALARY,
           DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
    FROM Employees
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = @Nhighest

SQLCopy

Above query I have change @Nhighest  variable values to 5 so it will return 5th highest salary from employee. This way you just need to change this variable only to get nth highest salary.

Find Nth highest salary department wise using common table expression.

Declare @Nhighest int
set @Nhighest = 2;

WITH RESULT AS (
SELECT *,
       DENSE_RANK() OVER(PARTITION BY DepartmentId ORDER BY Salary DESC) AS DENSERANK
    FROM Employees
 )
 SELECT distinct salary, d.DepartmentName
 FROM RESULT R
 join Department d on d.Id= DepartmentId
 WHERE DENSERANK = @Nhighest ;

SQLCopy

Using common table expression with dense_rank() and partition we can find department wise highest salary, here is highest salary of each department using above query. Here I have set @Nhighest value to 2 so it will return 2nd highest salary of each department.

In this article I have demonstrated how to find nth highest salary using different way. We have used max function, top keyword, sub-query and common table expression to find nth highest salary. Hope this article will help you find nth highest salary using the above methods. I have attached a single script for all queries with table creation and data insertion script. Please find other useful articles on SQL server topics that may help you to understand SQL concepts.