Each Department's Highest-Paid Instructor

For every department, find the instructor with the highest salary in that department. Return department (the department's name, aliased), first_name, last_name and salary, sorted by department. The maximum must be computed per department by a correlated subquery, not once for the whole college.

Expected result:

+------------------+------------+-----------+--------+
| department       | first_name | last_name | salary |
+------------------+------------+-----------+--------+
| Biology          | Amara      | Diallo    |  94000 |
| Business         | Olivia     | Grant     | 105000 |
| Computer Science | Grace      | Okafor    |  98500 |
| English          | Helen      | Brooks    |  91000 |
| Fine Arts        | Rafael     | Moreno    |  69000 |
| Mathematics      | Samuel     | Whitaker  | 101000 |
+------------------+------------+-----------+--------+
6 rows

Hints4 available

Topics:

Subqueries

Code Editor

Tab to indent (Esc then Tab to move on) · Ctrl+Enter to run · Ctrl+Space to expand shortcuts (sel, ins, desc)

Results

Run your SQL to see every statement's output here, then how it compares with the expected answer.

Not sure what is in a table? SHOW TABLES lists them and DESCRIBE students shows the columns — or open the database browser on the left.