Top N Per Group

Source data — Pattern 11: Interview SQL Patterns

All queries run against the shared employees table (see schema.sql). Topics 71 and 72 introduce an orders table to solve consecutive records and gaps-and-islands problems. Topics 73 and 74 introduce a monthly_sales table to demonstrate PIVOT and UNPIVOT operations.

---

What it does

Uses window functions to return the top records within each group.

Problem

Find the highest-paid employee in each department.

SELECT *
FROM (
  SELECT *,ROW_NUMBER() OVER(PARTITION BY department_id ORDER BY salary DESC) rn
  FROM employees
) t
WHERE rn=1;

Result