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 LIMIT: second highest salary per department

The query

SQL
SELECT
  d.dept_name,
  (SELECT salary
   FROM employees e
   WHERE e.dept_id = d.dept_id
   ORDER BY salary DESC
   LIMIT 1 OFFSET 1) AS second_highest_salary
FROM departments d
ORDER BY d.dept_name;
Tested against PostgreSQL 16

Note

“Correlated subquery with LIMIT OFFSET to find 2nd highest. OFFSET 1 skips the highest.

Tables referenced