← All tasksSQL · Middle

Top salary in each department

For each department list the employee with the highest salary: columns department, name, salary, ordered by department. If several people share the top salary, take the one with the smaller id: one row per department.

Tables

CREATE TABLE employees (
  id         int PRIMARY KEY,
  name       text NOT NULL,
  department text NOT NULL,
  salary     int NOT NULL
);

SELECT department, max(salary) AS salary
FROM employees
GROUP BY department;

Ctrl+Enter runs the tests. Tab indents; to leave the editor with the keyboard, press Esc and then Tab. Your code is saved in this browser.

Tests (3)

Each test fills the tables with its own data and compares your query's result with the expected one: the same columns (names and order) and the same rows in the same order, so ORDER BY matters. The query runs in real PostgreSQL right in your browser; the first run downloads it (about 5 MB).

INSERT INTO employees VALUES
  (1, 'Ann', 'Engineering', 7000),
  (2, 'Bob', 'Engineering', 9000),
  (3, 'Cid', 'Sales', 5000),
  (4, 'Dee', 'Sales', 6000),
  (5, 'Eva', 'HR', 4000);

-- a tie: the employee with the smaller id wins
INSERT INTO employees VALUES
  (7, 'Max', 'Sales', 6000),
  (3, 'Zoe', 'Sales', 6000),
  (5, 'Lea', 'Sales', 5000);

INSERT INTO employees VALUES (1, 'Solo', 'Legal', 3000);