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:
- 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.
- Main Query:
- Filters to return the salary where
rnis 2, which corresponds to the second highest salary.
- Filters to return the salary where
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:
Subquery:
SELECT MAX(Salary) FROM Employees: Finds the highest salary in the company.
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.