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.

Problem

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
JOIN (
    SELECT DepartmentId, MAX(Salary) AS MaxSalary
    FROM Employee
    GROUP BY DepartmentId
) AS MaxSalaries ON e.DepartmentId = MaxSalaries.DepartmentId AND e.Salary = MaxSalaries.MaxSalary;

Output:

Department	Employee	Salary
Sales	John	7000
Engineering	Jane	8000

Explanation:

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.