Department Highest Salary SQL Challenge - Oracle Solution

1. Introduction

In this blog post, we'll delve into a common Oracle SQL query problem: identifying the highest salary within each department. This type of analysis is crucial in HR and payroll management.


The objective is to write an Oracle SQL query that retrieves the employee with the highest salary from each department.

Database Table:

Employee Table:
Id Name Salary DepartmentId
1 John 9000 101
2 Doe 8500 102
Department Table:
Id Name
101 HR
102 IT

3. Oracle Solution

SELECT d.Name AS Department, e.Name AS Employee, e.Salary
FROM Employee e
JOIN Department d ON e.DepartmentId = d.Id
WHERE (e.DepartmentId, e.Salary) IN (
    SELECT DepartmentId, MAX(Salary)
    FROM Employee
    GROUP BY DepartmentId


Department Employee Salary
HR John 9000
IT Doe 8500


1. The query joins the Employee and Department tables on the DepartmentId.

2. It uses a subquery to find the maximum salary in each department.

3. The WHERE clause ensures that only those employees are selected whose salaries match the maximum salary found in the subquery for their respective departments.

4. This results in a list of employees who are the top earners in their departments.