Top 2 Earners per Department

Given employees and their salaries, return the two highest-paid people in each department. Ties in salary are broken by the lower id, and each department should yield at most two rows.

Schema

CREATE TABLE employees (
    id         INT,
    name       TEXT,
    department TEXT,
    salary     INT
);

Task

Return the top two earners per department. Rank by salary descending, breaking ties by id ascending. Output department, name, salary, and the within-department rank rn. Order the result by department ascending, then rn ascending.

Expected output

Approach

Number each employee within their department by descending salary using ROW_NUMBER, then keep the rows numbered 1 and 2. The numbering must live in a CTE (or subquery) because you cannot reference a window result in WHERE. ROW_NUMBER guarantees a strict 1, 2, 3, ... sequence, so filtering rn <= 2 returns exactly two rows per department even when salaries tie.

Query

WITH ranked AS (
    SELECT
        department,
        name,
        salary,
        ROW_NUMBER() OVER (
            PARTITION BY department
            ORDER BY salary DESC, id ASC
        ) AS rn
    FROM employees
)
SELECT department, name, salary, rn
FROM ranked
WHERE rn <= 2
ORDER BY department, rn;

Walkthrough

• Eng: Ann (150) → rn = 1; Bob and Cara both 140, and id ASC puts Bob (id 2) at rn = 2, Cara (id 3) at rn = 3. Filter keeps Ann and Bob.
• Sales: Eve and Finn both 120; id ASC gives Eve (id 5) rn = 1, Finn (id 6) rn = 2; Dan (90) is rn = 3 and drops out.
• HR: only Gil, rn = 1, so a single row survives — top-N naturally returns fewer than N when a group is smaller.
• Outer ORDER BY department, rn sorts departments alphabetically (Eng, HR, Sales) and orders each group by its rank.

Complexity & notes

• One sort per partition/order key drives the cost, roughly O(n log n). An index on (department, salary DESC, id) lets the engine produce rows already in window order.
• ROW_NUMBER vs RANK vs DENSE_RANK is the crux: with the two 120-salary Sales rows, RANK would give both rn = 1 and, filtering <= 2, you’d also catch Dan-style ties differently — use RANK/DENSE_RANK only when the requirement is “include everyone tied at the cutoff.” Here the cap of two rows demands ROW_NUMBER.
• The id ASC tie-break is what makes the choice between Bob and Cara (and Eve/Finn’s ordering) deterministic; without it the survivor would be arbitrary.
• Postgres/SQL Server/Oracle/MySQL 8+ all support this pattern identically. As an alternative, Postgres offers SELECT DISTINCT ON (department) ... for the top-1 case, but top-N generalizes cleanly only with ROW_NUMBER.