Deviopath › PostgreSQL

Top Two Earners per Department

Medium · Window Functions

A window function computes a value for each row from a group of related rows, without collapsing them. ROW_NUMBER() OVER (PARTITION BY x ORDER BY y) numbers the rows 1, 2, 3... separately inside each x. Window functions cannot go in WHERE, so filter in an outer query.

Return the two highest-paid employees of each department: department (its name), name, salary and rank_in_dept (1 or 2). Break salary ties by name ascending. Employees without a department are left out. Sort by department, then rank_in_dept.

Next: RANK vs DENSE_RANK