How do you approach SQL: Department Highest Salary (LeetCode 184)?

Approach 1: WHERE (department_id, salary) IN (SELECT department_id, MAX(salary) FROM Employee GROUP BY department_id).

What are common mistakes on SQL: Department Highest Salary (LeetCode 184)?

Filtering on an aggregate in WHERE rather than HAVING. An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders. COUNT(*) versus COUNT(column). The second skips nulls, and that difference is often the entire question.

gitGood.dev

← Back to problems

SQL: Department Highest Salary (LeetCode 184)

MediumDatabases

Description

> This problem is paper-only - see explanation for the canonical SQL solution. (LeetCode 184 classic.) Given `Employee(id, name, salary, department_id)` and `Department(id, name)`, return one row per department containing the highest-paid employee. If multiple employees tie for the top in a department, include all of them. **For the auto-grader:** return `[{ department, employee, salary }]` sorted by department ascending, employee ascending.

Example 1

Input: employees = [{id:1,name:"Joe",salary:90000,department_id:1},{id:2,name:"Jim",salary:90000,department_id:1},{id:3,name:"Henry",salary:80000,department_id:2}], departments = [{id:1,name:"IT"},{id:2,name:"Sales"}]

Output: [{department:"IT",employee:"Jim",salary:90000},{department:"IT",employee:"Joe",salary:90000},{department:"Sales",employee:"Henry",salary:80000}]

Constraints

- 0 <= employees.length, departments.length <= 10^4

SQL: Department Highest Salary (LeetCode 184): approach, complexity and pitfalls

A medium sql problem, graded against 3 test cases (1 of them hidden).

The pattern: SQL

Query-writing problems - joins, aggregation, window functions, and query tuning.

Reach for it when you see: A schema and a question about the data rather than a function signature.

More SQLproblems →

If you are stuck

Show the 2-step hint ladder

Approach 1: WHERE (department_id, salary) IN (SELECT department_id, MAX(salary) FROM Employee GROUP BY department_id).
Approach 2: RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) and keep rank = 1 (handles ties cleanly).

The optimal approach

Show the approach: Canonical SQL (window-function approach, handles ties):

```sql

WITH ranked AS (

SELECT e.name AS employee, e.salary, e.department_id,

RANK() OVER (PARTITION BY e.department_id ORDER BY e.salary DESC) AS rnk

FROM Employee e

)

SELECT d.name AS department, r.employee, r.salary

FROM ranked r

JOIN Department d ON d.id = r.department_id

WHERE r.rnk = 1

ORDER BY department, employee;

```

Why `RANK` not `ROW_NUMBER`: the spec says "include all employees who tie for the top". `ROW_NUMBER` would arbitrarily pick one. `RANK` and `DENSE_RANK` both work here (they only differ when there's a 3rd-place after a tie, which doesn't matter for rank = 1).

`(col1, col2) IN (...)` tuple form is cleaner for simple cases but doesn't extend to "top N per group" - the window-function form does.

The full reference solution in every supported language stays in the editor above - reveal it there once you have had a real attempt.

What the tests check

Read off this problem's own test suite, so these are the cases a submission actually has to survive.

Empty input. At least one case passes an empty collection. Make sure your loop bounds and any "first element" read survive a length of zero.
Single element. A one-element case is included. Two-pointer and window solutions frequently return the wrong thing when the two indices start out equal.

Common SQL mistakes

These apply to the pattern as a whole, not just this problem.

Filtering on an aggregate in `WHERE` rather than `HAVING`.
An inner join where a left join is meant, which silently drops the rows the question was about - the customers with zero orders.
`COUNT(*)` versus `COUNT(column)`. The second skips nulls, and that difference is often the entire question.