Find the Second Highest Salary in the Company Using SQL Server

Find the Second Highest Salary in the Company Using MS SQL Server Query?

To find the second highest salary in a company using Microsoft SQL Server, you can use several approaches. Below, I'll describe two common methods: using the ROW_NUMBER() window function and using a subquery with DISTINCT and TOP.

Method 1: Using ROW_NUMBER()

This method assigns a unique row number to each salary in descending order and then selects the salary with row number 2.

WITH RankedSalaries AS (

    SELECT

        Salary,

        ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn

    FROM Employees

)

SELECT Salary

FROM RankedSalaries

WHERE rn = 2;

Explanation:

1. CTE RankedSalaries:
   • ROW_NUMBER() OVER (ORDER BY Salary DESC) AS rn: Assigns a unique sequential integer starting at 1 to each salary, ordered from highest to lowest.
2. Main Query:
   • Filters to return the salary where rn is 2, which corresponds to the second highest salary.

Method 2: Using TOP and Subquery

This method involves using a subquery to first exclude the highest salary and then find the top salary from the remaining salaries.

SELECT MAX(Salary) AS SecondHighestSalary

FROM Employees

WHERE Salary < (

    SELECT MAX(Salary)

    FROM Employees

);

Explanation:

1. Subquery:

   • SELECT MAX(Salary) FROM Employees: Finds the highest salary in the company.

2. Main Query:

   • WHERE Salary < (subquery): Filters out the highest salary and then finds the maximum salary from the remaining salaries, which is the second highest salary.

Example Data and Results

Assume the Employees table has the following salaries:

Salary
100000
120000
110000
95000
90000

Using the ROW_NUMBER() method:

• Highest salary is 120000, second highest is 110000.

Using the TOP and Subquery method:

• The subquery finds the highest salary (120000), then the main query finds the maximum of the remaining salaries (110000).

Both methods will correctly return the second highest salary, which in this case is 110000.

You can run these queries in SQL Server Management Studio or any SQL query tool connected to your SQL Server to determine the second highest salary in the company.