Vol. I · A Postgres Handbook

The Query & the Schema

A reading-room reference for writing PostgreSQL by hand — selects to window functions, with the schema in plain view.

IntermediateSubqueries & EXISTS

Subquery with aggregation to find highest-earning dept

The query

SQL
SELECT dept_name, location
FROM departments
WHERE dept_id = (
  SELECT dept_id
  FROM employees
  GROUP BY dept_id
  ORDER BY AVG(salary) DESC
  LIMIT 1
);
Tested against PostgreSQL 16

Note

“Subquery that returns single value via LIMIT 1 used as scalar comparison.

Tables referenced