MySQL Query to Rank Employees by Salary in Each Department

Partition Employees by Department and Rank by Salary

Write a MySQL query to partition employees by department and rank them by salary within each department.

Solution:

-- Start the SELECT statement to define the columns to be retrieved
SELECT 
    -- Retrieve the DepartmentID column to identify the department for each employee
    DepartmentID, 
    -- Retrieve the EmployeeID column as a unique identifier for each employee
    EmployeeID, 
    -- Retrieve the Name column to display the employee's name
    Name, 
    -- Retrieve the Salary column to show each employee's salary
    Salary,
    -- Use the RANK() window function to rank employees within each department
    -- PARTITION BY DepartmentID groups the data by department
    -- ORDER BY Salary DESC orders salaries from highest to lowest within each department,
    -- so the highest salary gets a rank of 1
    RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC) AS DeptSalaryRank
-- Specify the source table containing the employee records
FROM Employees; 

Explanation:

• Purpose of the Query:
• The goal is to rank employees by salary within each department.
• This demonstrates the use of the PARTITION BY clause in conjunction with the RANK() function.
• Key Components:
• RANK() OVER (PARTITION BY DepartmentID ORDER BY Salary DESC): Ranks employees by salary within each department.
• SELECT DepartmentID, EmployeeID, Name, Salary: Retrieves the department and employee details along with their rank.
• Why use Window Functions?:
• Window functions allow you to perform calculations within specific partitions of your data, making it easier to analyze subsets of your data.
• Real-World Application:
• For example, in a company, you might want to identify the top earners within each department for performance reviews or bonuses.

For more Practice: Solve these Related Problems:

• Write a MySQL query to rank employees within their job title instead of department.
• Write a MySQL query to partition employees by department and find the lowest salary in each department.
• Write a MySQL query to assign a unique rank to each employee within a department without skipping ranks.
• Write a MySQL query to identify employees who have the same salary within each department.

Go to:

• Master MySQL Window Functions and CTEs Exercises Home. ↩
• MySQL Exercises Home ↩

PREV : Partition Employees by Department and Rank by Salary.
NEXT : Find the Top 3 Highest Paid Employees in Each Department.

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