Write a query that finds the highest and lowest paid employee in each department.
You work out the highest and the lowest for each department, and then you keep the rows that match one of them.
CREATE TABLE employees (
id INTEGER PRIMARY KEY,
name VARCHAR(120) NOT NULL,
department_id INTEGER NOT NULL,
salary_cents INTEGER NOT NULL
);
INSERT INTO employees (id, name, department_id, salary_cents) VALUES
(1, 'Ada', 1, 900000),
(2, 'Grace', 1, 700000),
(3, 'Alan', 1, 700000),
(4, 'Katherine', 2, 600000),
(5, 'Margaret', 2, 600000);
-- The highest and the lowest for each department.
WITH ranked AS (
SELECT
id,
name,
department_id,
salary_cents,
MAX(salary_cents) OVER (PARTITION BY department_id) AS dept_max,
MIN(salary_cents) OVER (PARTITION BY department_id) AS dept_min
FROM employees
)
SELECT id, name, department_id, salary_cents,
CASE WHEN salary_cents = dept_max THEN 'highest'
WHEN salary_cents = dept_min THEN 'lowest'
ELSE 'other' END AS position
FROM ranked
WHERE salary_cents = dept_max OR salary_cents = dept_min
ORDER BY department_id, salary_cents DESC;
id name department_id salary_cents position
1 Ada 1 900000 highest
2 Grace 1 700000 lowest
3 Alan 1 700000 lowest
4 Katherine 2 600000 highest and lowest
5 Margaret 2 600000 highest and lowest
So what is happening? The window function works out the biggest and the smallest salary inside each department, and puts both numbers beside every row. The outer query then keeps the rows where the salary matches one of them.
Notice what happened with department two. It has two employees and they are paid the same, so each row is both the highest and the lowest. That is not a mistake in the query, it is the truth about the data, and a report that showed only one of them would be hiding something.
The next question in this topic is the classic one that everybody is asked.