Return all highest-paid employees per department
Return department ID, employee ID, and salary for all employees tied for the maximum non-NULL salary in each non-NULL department. Exclude departments with only NULL salaries. Return only the requested columns and follow the specified ordering. This question probes join grouped maxima back to base rows so that ties are retained.
Input
Setup script defining employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT).
Output
department_id, id, salary; ascending department_id then id.
Constraints
0–10000 rows per table. IDs are unique and non-NULL unless stated otherwise; foreign keys may be NULL. Numeric amounts are integers from 0 to 1000000. Dates are valid YYYY-MM-DD text. Text uses case-sensitive comparison; test data has consistent case. Duplicate business values and NULLs are allowed unless excluded by the task.
Example
Tables and sample rows
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'Ana',10,NULL,100),(2,'Ben',10,1,200),(3,'Cara',20,1,200),(4,'Dan',20,3,300),(5,'Eve',NULL,NULL,NULL);
Expected result
department_id | id | salary 10 | 2 | 200 20 | 4 | 300
Another dataset (1)
CREATE TABLE employees(id INT, name VARCHAR(30), department_id INT, manager_id INT, salary INT); INSERT INTO employees VALUES (1,'Solo',10,NULL,100),(2,'Twin',10,1,100),(3,'Null',NULL,NULL,NULL);
Expected result
department_id | id | salary 10 | 1 | 100 10 | 2 | 100
Covers missing matches, ties, or NULL values without relying on insertion order.
Hints (2)
- Compute MAX per department.
- Match both department and salary, not salary alone.
Target complexity: time O(n) · space O(n)