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 Problem OverviewEmployee TablenamesalarydeptIdJoe850001Max900001Henry800002Departmentidname1IT2SalesJOIN + RANKMAX per deptResult: Department LeadersDepartmentEmployeeSalaryITMax90000SalesHenry80000Find the highest paid employee(s) in each department
Understanding the Visualization
1
Join Tables
Combine Employee and Department data
2
Rank/Filter
Find highest salary in each department
3
Result
Return department leaders
Key Takeaway
🎯 Key Insight: Use window functions or subqueries to find group maximums efficiently

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