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 nth highest salary in sql using dense_rank.
  • Select nth highest salary in sql using rank() function.
  • Sometimes, find the solution without using any inbuilt function of SQL.

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.

Create Sample Data with 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.

nth highest salary in SQL using dense_rank

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
how to find second highest salary in sql using rank() function

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
How to Find 2nd, 3rd, or Nth highest salary in SQL With Dense_Rank & Max Function 1

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.

Complete Guide on Common Table Expression

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.

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.

  1. What is CTE or Common Table Expression in Microsoft SQL?

    CTE stands for common table expressions that are temporary(they are called temporary as results are not stored anywhere and get lost as soon as a query’s execution is completed) named result sets, derived from a simple query that can be referenced within a single SELECT, INSERT, UPDATE, DELETE or MERGE statement. CTEs can include references to themselves which are recursive common table expressions.

  2. What is Rank() Function in Microsoft SQL?

    The RANK() function is a window function that could be used in SQL Server to calculate a rank for each row within a partition of a result set. 

  3. What is Dense_Rank in Microsoft SQL Server?

    RANK and DENSE_RANK will assign the grades the same rank depending on how they fall compared to the other values. However, RANK will then skip the next available ranking value whereas DENSE_RANK would still use the next chronological ranking value.

77 / 100

2 Comments

Comments are closed