Top N Per Group
Pick the best N rows inside each partition. The canonical shape: rank each row within its partition with ROW_NUMBER (or RANK / DENSE_RANK depending on tie behavior), then filter to rn <= n. Cannot be done with a plain GROUP BY because you need full rows, not aggregates.
Syntax #
WITH ranked AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY group_col
ORDER BY metric_col DESC, tie_breaker
) AS rn
FROM table_name
)
SELECT *
FROM ranked
WHERE rn <= n;Example #
Always include a tie-breaker in the window ORDER BY. Without one, ROW_NUMBER assigns ranks arbitrarily among tied values, so reruns of the same query can return different rows at the cutoff. A unique-enough secondary sort (usually id) makes the result reproducible. Second trap: pick the right ranking function for ties in the output. ROW_NUMBER = exactly N rows per group (picks one arbitrary row on ties). RANK / DENSE_RANK = all tied rows included (can exceed N). Business question determines the choice.