Find the Top 3 Highest Salaries per Department Using SQL Server

 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:

1. 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 DepartmentID ensures the ranking is reset for each department.
   • ORDER BY Salary DESC orders salaries within each department from highest to lowest.

2. 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 DepartmentID and rank (rn) to ensure the output is organized.

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.