MySQL Query to Find the Highest Salary Employees per Department

Find the Employees with the Highest Salary in Each Department

Write a MySQL query to find the employees with the highest salary in each department using a window function.

Solution:

-- Define a Common Table Expression (CTE) named DepartmentMaxSalaries to rank employees by salary within each department
WITH DepartmentMaxSalaries AS (
    -- Begin the inner SELECT statement to retrieve employee details and compute salary ranks
    SELECT 
        -- Retrieve the DepartmentID to group employees by 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 to show the employee's current salary
        Salary,
        -- Calculate the salary rank within each department:
        -- PARTITION BY DepartmentID groups employees by department
        -- ORDER BY Salary DESC ranks employees in descending order of salary
        -- The employee with the highest salary in each department 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 earners from each department
SELECT 
    -- Retrieve the DepartmentID to display the department each employee belongs to
    DepartmentID, 
    -- Retrieve the EmployeeID to uniquely identify each 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 as the previously defined CTE 'DepartmentMaxSalaries'
FROM DepartmentMaxSalaries
-- Filter the results to include only the employees with the highest salary in each department (SalaryRank = 1)
WHERE SalaryRank = 1;

Explanation:

• Purpose of the Query:
• The goal is to identify the employees with the highest salary in each department.
• This demonstrates the use of a CTE and the RANK() window function.
• Key Components:
• RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC): Ranks employees by salary within each department.
• WITH DepartmentMaxSalaries AS (...): Defines a CTE to store the ranked employees.
• WHERE SalaryRank = 1: Filters the results to include only the top-ranked 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 highest-paid employees in each department for recognition or reward purposes.

For more Practice: Solve these Related Problems:

• Write a MySQL query to find the employees with the lowest salary in each department.
• Write a MySQL query to list employees whose salary is within the top 10% of their department.
• Write a MySQL query to compute the difference between an employee’s salary and the department’s maximum salary.
• Write a MySQL query to find the average salary for each department and rank them in descending order.

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

Previous MySQL Exercise: Calculate the Average Sales Over a Rolling 3-Month Window.