Find Nth Highest Salary Using dense_rank & Max Function | 2023

We can utilize SQL inbuild functions to find the nth highest salary using dense_rank, rank, and max functions. In this post, we will explore four ways to pick the second-highest salary in MS SQL. For SQL, this is a very frequently asked query during a Job interview.

Among all the interviews I’ve had in my career. Every time, the same question was asked. Only the way of asking was different though.

• How to find the nth highest salary in sql using dense_rank.
• Select the nth highest salary in sql using the rank() function.
• Sometimes, find the solution without using any inbuilt function of SQL.

Create Sample Data with the Highest and Second highest Salary. I have created one table called Employee with three columns Id, Name, and Salary,  We will be using the same table for all solutions.

Find Second Highest Salary in SQL using Max() function

As you can see in the above table, an Employee with the name D takes the second-highest salary among all.

SQL Server MAX() the function is an aggregate function that returns the maximum value in a set.

Select the salary column using the SQL Server MAX() function.

SELECT Max(salary) AS Salary
FROM   employee
WHERE  salary < (SELECT Max(salary)
FROM   employee)

Select salary column along with Id and Name

SELECT TOP 1 id,
NAME,
Max(salary)
FROM employee
WHERE salary < (SELECT Max(salary)
FROM employee)
GROUP BY id,
NAME,
salary
ORDER BY salary DESC

Inline SQL Query to Find Nth Highest Salary

We can use an inline query to select the second-highest or nth-highest salary in the table. You just have to replace the number in the inline query.

SELECT TOP 1 *
FROM   (SELECT DISTINCT TOP 2 salary
        FROM   employee
        ORDER  BY salary DESC) AS t
ORDER  BY t.salary ASC

You can just put 3 in the inline query, In case you want to select the 3rd highest salary.

Find Nth highest salary in SQL Using Dense_Rank

DENSE_RANK gives you the ranking within your ordered partition, but the ranks are consecutive. No ranks are skipped if there are ranks with multiple items.

select Id,Name,Salary, DENSE_RANK() over(order by salary desc) as Rank
from Employee

Using Dense_Rank() function we can assign a rank to the salary column order by desc.

As you can see in the below output

E got 1st rank,

D got rank 2,

but B and F got the same rank because both the employees have the same salary.

In order to get the 2nd highest salary you just need to keep the above query as an inline query and place the where condition with Rank=2

select top 1 Id,Name,Salary  from (
select Id,Name,Salary, DENSE_RANK() over(order by salary desc) as R
from Employee) result
where result.R=2

As a result of the above query

Find Nth highest salary in SQL Using Dense_Rank and Common Table Expression

CTE or common table expression is a temporary named result set that you can reference within a SELECT, INSERT, UPDATE, or DELETE statement.

We are using the same Dense_Rank() query used in the previous solution. This time we will be using CTE instead of an inline query.

with tmp_emp as(
select Id,Name,Salary, DENSE_RANK() over(order by salary desc) as Rank
from Employee)

The above query will keep the result in the temp result set using CTE or Common Table Expression.

Understand CTE in Detail With Example

Now, we just need to use a tmp_emp table with where clause and number to get the nth highest salary.

select Id,Name,Salary from tmp_emp where tmp_emp.Rank=2

Conclusion

There can be multiple ways to solve every problem. We tried solving the same problem with these three approaches. If you know any better approach feel free to provide it in the comment section.