Find the Top 3 Highest Salaries per Department Using MS SQL Server Query?
To find the top 3 highest salaries per department in SQL Server, you can use the ROW_NUMBER() window function. This function helps you assign a unique sequential integer to rows within a partition of the result set, making it easy to rank salaries within each department.
Here’s how you can achieve this:
SQL Query Using ROW_NUMBER()
Assuming you have a table named Employees with columns EmployeeID, DepartmentID, and Salary, you can use the following query:
WITH RankedSalaries AS (
SELECT
EmployeeID,
DepartmentID,
Salary,
ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS rn
FROM Employees
)
SELECT
EmployeeID,
DepartmentID,
Salary
FROM RankedSalaries
WHERE rn <= 3
ORDER BY DepartmentID, rn;
Explanation:
Common Table Expression (CTE) -
RankedSalaries:ROW_NUMBER() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS rn: This assigns a unique rank (rn) to each salary within its department, ordered from highest to lowest.PARTITION BY DepartmentIDensures the ranking is reset for each department.ORDER BY Salary DESCorders salaries within each department from highest to lowest.
Main Query:
- Filters the results from the CTE to include only those rows where the rank (
rn) is less than or equal to 3, which means you get the top 3 salaries per department. - Orders the final result by
DepartmentIDand rank (rn) to ensure the output is organized.
- Filters the results from the CTE to include only those rows where the rank (
Alternative: Using TOP and PERCENTILE_CONT
Another approach involves using PERCENTILE_CONT in a more complex query, but the ROW_NUMBER() method is generally simpler and more direct for this task.
You can run the provided query in SQL Server Management Studio or any SQL query tool connected to your SQL Server to get the top 3 highest salaries per department.