MySQL Query to get Top 3 Highest Paid Employees in Each Department

Find the Top 3 Highest Paid Employees in Each Department

Write a MySQL query to find the top 3 highest paid employees in each department using a window function.

Solution:

-- Define a Common Table Expression (CTE) named RankedEmployees to rank employees by salary within each department
WITH RankedEmployees AS (
    -- Begin the inner SELECT statement to retrieve employee details and compute their salary rank
    SELECT 
        -- Retrieve the DepartmentID to indicate the employee's department
        DepartmentID, 
        -- Retrieve the EmployeeID as the unique identifier for each employee
        EmployeeID, 
        -- Retrieve the Name to display the employee's name
        Name, 
        -- Retrieve the Salary of the employee
        Salary,
        -- Calculate the salary rank within each department:
        -- PARTITION BY DepartmentID groups employees by department
        -- ORDER BY Salary DESC orders salaries in descending order, so the highest salary gets rank 1
        RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS SalaryRank
    -- Specify the source table containing employee records
    FROM Employees
)
-- Begin the main SELECT statement to retrieve the top employees from each department
SELECT 
    -- Retrieve the DepartmentID to show the employee's department
    DepartmentID, 
    -- Retrieve the EmployeeID for a unique identification of the employee
    EmployeeID, 
    -- Retrieve the Name to display the employee's name
    Name, 
    -- Retrieve the Salary to show the employee's salary
    Salary
-- Specify the source of the data as the previously defined CTE 'RankedEmployees'
FROM RankedEmployees
-- Filter the results to include only those employees with a salary rank of 3 or better (top 3 per department)
WHERE SalaryRank <= 3; 

Explanation:

• Purpose of the Query:
• The goal is to identify the top 3 highest paid employees in each department.
• This demonstrates the use of a Common Table Expression (CTE) in conjunction with the RANK() window function.
• Key Components:
• RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC): Ranks employees by salary within each department.
• WITH RankedEmployees AS (...): Defines a CTE to store the ranked employees.
• WHERE SalaryRank <= 3: Filters the results to include only the top 3 highest paid employees in each department.
• Why use CTEs and Window Functions?:
• CTEs make complex queries more readable and manageable by breaking them down into simpler parts.
• Window functions allow you to perform ranking and partitioning within the CTE.
• Real-World Application:
• For example, in a company, you might want to identify the top earners in each department for recognition or reward purposes.

For more Practice: Solve these Related Problems:

• Write a MySQL query to find the top 5 highest paid employees in each department.
• Write a MySQL query to find the highest-paid employee for each job title.
• Write a MySQL query to find employees who are among the top 10% highest paid in their department.
• Write a MySQL query to list employees whose salary is above the department average.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.

Previous MySQL Exercise: Calculate Cumulative Percentage of Total Sales.
Next MySQL Exercise: Calculate the Difference between Each Employee's Salary and the Department Average.