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 the 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.
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.
MAX() the function is an aggregate function that returns the maximum value in a set.
Select the salary column using the SQL Server
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.
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
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.
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.
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.
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.