Select the top N rows per group — Snippet

Select the top N rows per group snippet

Keep the top N rows inside every group — the top 3 earners per department, the latest order per customer.

Keep the top N rows inside every group — the top 3 earners per department, the latest order per customer. GROUP BY collapses each group to one row, which is exactly wrong here: rank instead. ROW_NUMBER() OVER (PARTITION BY the group ORDER BY the ranking) numbers the rows inside each group, so filtering on the rank keeps exactly N per group. Swap ROW_NUMBER() for RANK() when tied rows should both make the cut.

Runnable recipe · 1 languages

Files & Datasqlwindow-functionsgrouping

Every language

1 implementations, copy-ready. One at a time with syntax highlighting, or all inline.

SQLSQLrunnable

-- Top 3 earners per department: rank inside each group, keep rn <= 3.
WITH ranked AS (
  SELECT
    department,
    name,
    salary,
    ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rn <= 3
ORDER BY department, salary DESC;

Runs as-is in the playground's SQLite (window functions need SQLite 3.25+). RANK() keeps ties — two rows share rank 1 and both survive an rn <= N filter — while ROW_NUMBER() always breaks ties arbitrarily.

Run in the SQL playground →

Keep going

Read the sql-window-functions cheatsheet →