How To Find The Second Highest Salary In SQL Server

Introduction

In this article, I am going to explain to you how to find the second highest salary in various ways. This is one of the most common questions asked in an SQL interview.

Below are the several ways of finding the Nth highest salary:

How to find the second highest salary in SQL Server using a Sub-Query
How to find the second highest salary in SQL Server using a CTE
How to find the 3rd, 5th highest salary

Here we will be using SQL Server 2017, or you can use SQL Server 2008 or above.

Read my previous Joins in SQL Server 2017 part of this article using the below links before reading this article:

Prerequisites

SQL Server 2017 or you can use SQL server 2008 or above version.

First, we will create a Database and a table.

Creating a Database and One Table

Step 1 - Create a Database

Open your SQL Server and use the following script to create the “EmployeeDB” Database.

Create database EmployeeDB

Now, select the script query then press F5 or click on the Execute button to execute the above script.

You should see a message, “Command(s) completed successfully.” This means your new database has been created.

Step 2 - Create a table

Open your SQL Server and use the following script to create a table “tbl_Employees”.

Create table tbl_Employees
(
Id int primary key not null identity(1,1),
FirstName varchar(50),
LastName varchar(20),
Location varchar(20),
Gender varchar(50),
Salary int
)

Execute the above query to create “tbl_Employees “.

You should see a message, “Command(s) completed successfully.”

Now, the data has been inserted into the table.

Insert into tbl_Employees values ('Chittaranjan', 'Swain','Odisha', 'Male', 80000)
Insert into tbl_Employees values ('Chandin', 'Swain', 'Pune','Female', 76000)
Insert into tbl_Employees values ('Mitu', 'Pradhan','Delhi', 'Male', 55000)
Insert into tbl_Employees values ('Jeni', 'Swain','Chennai', 'Female', 76000)
Insert into tbl_Employees values ('Adyashree', 'Swain','UK', 'Female', 49000)
Insert into tbl_Employees values ('Ram', 'Kumar','US', 'Male', 39000)
Insert into tbl_Employees values ('Jitendra', 'Gouad','Hydrabad', 'Male', 35000)
Insert into tbl_Employees values ('Dibas', 'Hembram','Bangalore', 'Male', 55000)

Execute the above query, you should see a message, “Command(s) completed successfully.”

Now retrieve all data from the “tbl_Employees” table.

select * from tbl_Employees

output

How To Find Second Highest Salary

select distinct top 2 salary from tbl_Employees order by Salary desc

Output

How To Find Second Highest Salary Using a Sub-Query

To Get the Second Highest Salary use a Subquery along with the Max() function

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

Output

How To Find Second Highest Salary Using a Sub-Query

SELECT TOP 1 SALARY
FROM (
      SELECT DISTINCT TOP 2 SALARY
      FROM tbl_Employees
      ORDER BY SALARY DESC
      ) RESULT
ORDER BY SALARY

OutPut

How To Find Second Highest Salary Using CTE

WITH RESULT AS
(
SELECT SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
FROM tbl_Employees
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = 2

Output

How To Find the Third Highest Salary

select distinct top 3 salary from tbl_Employees order by Salary desc

Output

How To Find the Third Highest Salary Using Sub-Query

SELECT TOP 1 SALARY
FROM (
      SELECT DISTINCT TOP 3 SALARY
      FROM tbl_Employees
      ORDER BY SALARY DESC
      ) RESULT
ORDER BY SALARY

Output

How To Find the Third Highest Salary Using CTE

WITH RESULT AS
(
SELECT SALARY,
DENSE_RANK() OVER (ORDER BY SALARY DESC) AS DENSERANK
FROM tbl_Employees
)
SELECT TOP 1 SALARY
FROM RESULT
WHERE DENSERANK = 3

Output

Similarly, to find the 5th highest salary simply replace N with 5.

Conclusion

In this article, I explained how to find the second highest salary with several ways in SQL Server with examples. I hope this article has helped you to understand this topic.

Post your valuable feedback in the comments section.

Search This Blog