Department Highest Salary SQL Challenge - MySQL Solution

1. Introduction

In this blog post, we will explore a common SQL query in MySQL databases – identifying the highest salary in each department. This is a typical requirement in HR and payroll management systems.


The task is to write a MySQL query that retrieves the highest salary from each department in a company's database, which includes tables for employees and departments.

Database Table:

Employee Table:
Id Name Salary DepartmentId
1 John 7000 101
2 Jane 8000 102
Department Table:
Id Name
101 Sales
102 Engineering

3. MySQL Solution

SELECT d.Name AS Department, e.Name AS Employee, e.Salary
FROM Employee e
JOIN Department d ON e.DepartmentId = d.Id
    SELECT DepartmentId, MAX(Salary) AS MaxSalary
    FROM Employee
    GROUP BY DepartmentId
) AS MaxSalaries ON e.DepartmentId = MaxSalaries.DepartmentId AND e.Salary = MaxSalaries.MaxSalary;


Department Employee Salary
Sales John 7000
Engineering Jane 8000


1. The query performs a join between the Employee and Department tables.

2. A subquery (MaxSalaries) is used to find the maximum salary in each department.

3. The main query joins the Employee table with MaxSalaries to find the employees who earn the maximum salary in their respective departments.

4. The result includes the department name, employee name, and their salary.