Department Highest Salary - Problem

You are given two tables: Employee and Department.

The Employee table contains information about employees including their ID, name, salary, and department ID. The Department table contains department information with ID and name.

Write a SQL solution to find employees who have the highest salary in each department. If multiple employees share the highest salary in a department, include all of them.

Return the result table in any order with columns: Department, Employee, and Salary.

Table Schema

Employee
Column Name Type Description
id PK int Primary key, unique employee ID
name varchar Employee name
salary int Employee salary
departmentId int Foreign key referencing Department.id
Primary Key: id
Department
Column Name Type Description
id PK int Primary key, unique department ID
name varchar Department name
Primary Key: id

Input & Output

Example 1 — Multiple Departments
Input Tables:
Employee
id name salary departmentId
1 Joe 85000 1
2 Henry 80000 2
3 Sam 60000 2
4 Max 90000 1
Department
id name
1 IT
2 Sales
Output:
Department Employee Salary
IT Max 90000
Sales Henry 80000
💡 Note:

In the IT department, Max has the highest salary (90000). In Sales department, Henry has the highest salary (80000). Each department's top earner is returned.
Example 2 — Salary Tie
Input Tables:
Employee
id name salary departmentId
1 Joe 70000 1
2 Jim 90000 1
3 Henry 80000 2
4 Sam 90000 1
Department
id name
1 IT
2 Sales
Output:
Department Employee Salary
IT Jim 90000
IT Sam 90000
Sales Henry 80000
💡 Note:

In the IT department, both Jim and Sam share the highest salary (90000), so both are included in the result. Henry is the sole highest earner in Sales with 80000.

Constraints

  • 1 ≤ Employee.id, Department.id ≤ 100
  • 1 ≤ Employee.salary ≤ 100000
  • Employee.name and Department.name are non-null varchar
  • Each departmentId references a valid department

Visualization

Tap to expand
Department Highest Salary INPUT Employee Table id name salary deptId 1 Joe 70000 1 2 Jim 90000 1 3 Henry 80000 2 4 Sam 60000 2 5 Max 90000 1 Department Table id name 1 IT 2 Sales Find highest salary per department ALGORITHM STEPS 1 JOIN Tables Link Employee with Department ON e.deptId = d.id 2 GROUP BY Department Calculate MAX(salary) for each department 3 Subquery or CTE Find max salary per dept WHERE (deptId, salary) IN ... 4 Return Results Dept name, Emp name, Salary Handles ties (multiple max) IT: 90000 Sales: 80000 Match employees with max salary FINAL RESULT Output Table Department Employee Salary IT Jim 90000 IT Max 90000 Sales Henry 80000 OK - Correct Output 3 rows (includes ties) -- Optimal Solution SELECT d.name, e.name, e.salary FROM Employee e JOIN Department d WHERE (deptId,sal) IN (SELECT..MAX..GROUP) Key Insight: Use a subquery to find MAX(salary) grouped by departmentId, then JOIN back to get employee names. This handles TIES correctly - multiple employees can share the highest salary in a department. Alternative: Use RANK() or DENSE_RANK() window function with PARTITION BY departmentId. TutorialsPoint - Department Highest Salary | Optimal Solution Time: O(n) | Space: O(n) where n = number of employees

Related Problems

Asked in
Amazon 25 Facebook 18 Google 15 Apple 12
78.0K Views
High Frequency
~18 min Avg. Time
2.2K Likes
Ln 1, Col 1
Smart Actions
💡 Explanation
AI Ready
💡 Suggestion Tab to accept Esc to dismiss
// Output will appear here after running code
Code Editor Closed
Click the red button to reopen