Skip to content
Quantoros

How do you get the top N rows per group?

SQLLevel: IntermediateWindow Functions

How do you get the top N rows per group?

Hints
  • Define one output row and the source-table keys.
  • Check duplicates, NULLs and the order of filtering and aggregation.

Answer

Use ROW_NUMBER() with PARTITION BY and then filter in a CTE or subquery.

WITH ranked AS (
    SELECT
        name,
        department,
        salary,
        ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
    FROM employees
)
SELECT name, department, salary
FROM ranked
WHERE rn <= 3;  -- Top 3 per department

You can't put window functions in WHERE directly (they run after WHERE), so you need a subquery or CTE wrapper.

Source and licence: SQL Interview Questions — Arda Kocadoru · MIT

Related questions