Department Top Three Salaries SQL Challenge - MySQL Solution

1. Introduction

This blog post examines how to identify the top three salaries in each department using MySQL. This kind of query is particularly useful in HR analytics for performance and compensation analysis.


The task is to write a MySQL query that lists the top three salaries in each department of a company.

Database Table:

Employee Table:
Id Name Salary DepartmentId
1 John 1000 101
2 Jane 2000 101
Department Table:
Id Name
101 Sales

3. MySQL Solution

SELECT d.Name AS Department, e.Name AS Employee, e.Salary
    SELECT Name, Salary, DepartmentId,
           DENSE_RANK() OVER (PARTITION BY DepartmentId ORDER BY Salary DESC) as rank
    FROM Employee
) e
JOIN Department d ON e.DepartmentId = d.Id
WHERE e.rank <= 3;


Department Employee Salary
Sales Jane 2000
Sales John 1000


1. The query uses a window function (DENSE_RANK()) to rank the salaries within each department.

2. It partitions the data by DepartmentId and orders by Salary in descending order.

3. The outer query then joins this ranked data with the Department table.

4. The WHERE clause limits the results to employees with a rank of 3 or less, effectively showing the top three salaries in each department.