In this article, we are going to discuss four ways to select second highest salary in MS SQL. This is a very commonly asked interview question in almost all the interviews.

Who is the audience

Anyone who works on a “Microsoft SQL server” may be a developer, Tester, and DBA many more. Because, I will be using some inbuilt functions of MS SQL, which might not be available in other database tools like Oracle, My SQL, PostgreSQL etc.

Don’t get me wrong, Offcource other database tools might have these functions, but with different name and syntaxes.     

Let’s jump to the solutions

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

SQL table,select second highest salary in SQL

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

Using Max() function

The MAX() is an MS SQL inbuild function can be used for returning the largest value of the selected column.

Select only salary column

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

Using Inline SQL query

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.

Using Dense_Rank(), MS SQL inbuilt function & Inline query

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 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

A result of the above query

 

 

Using Dense_Rank(), MS SQL inbuilt function & CTE

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)

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

 

 

We hope this article was helpful to you. If you know any other way to get the nth highest salary. Feel free to provide your solution in the below comments section.

Subscribe to our newsletter today to receive updates on latest news tech news, tutorials and free blog posting and SEO tips

 

 

Close

About The Author

Deependra Kushwah
Deependra kushwah is a member of the fastest growing bloggers community "betechnical", Author, Youtuber, and hardcore Coder. I love writing code in different languages, I also write blogs on tech tutorials, gadgets review and also post some technical videos on youtube on many topics.

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.