window

Top-N Per Group With ROW_NUMBER

Use a CTE with ROW_NUMBER() to keep only the two highest-paid employees in each department.

Overview

A classic reporting need is "the top N rows per group." The cleanest pattern is a CTE that assigns ROW_NUMBER() OVER (PARTITION BY … ORDER BY …), then an outer query that filters WHERE rn <= N. Here we keep only the two highest-paid employees in each department.

The Query

Loading playground environment...

Step-by-Step Breakdown

ranked CTE: numbers employees 1, 2, 3… within each department by salary descending.
WHERE rn <= 2 in the outer query keeps only the top two per department.
Because ROW_NUMBER is unique, ties don't both survive. Use RANK/DENSE_ RANK instead if you want all tied rows.

ROW_NUMBER vs RANK for top-N

ROW_NUMBER strictly limits to N rows; RANK may return more than N if there are ties at the boundary. Choose based on whether ties should be included.

Variations

Top 3 movies by budget per genre:

Loading playground environment...

Common Mistakes

Filtering rn in the CTE's WHERE. You can't reference rn inside the CTE that defines it; filter in the outer query.
Using ROW_NUMBER when ties matter. Switch to RANK to keep tied rows at the boundary.
Forgetting PARTITION BY. Without it, you get the global top-N, not per group.

Related examples

Multiple CTEs in One Query

cte

Chain several CTEs to compute department stats then rank departments by average salary.

RANK() Per Group With PARTITION BY

window

Rank employees by salary within each department using RANK() OVER (PARTITION BY ...).

ROW_NUMBER(): Enumerating Rows Within Groups

window

Assign a sequential number to each employee within their department, ordered by salary.

Cite this resource

SQLSimplified. "Top-N Per Group With ROW_NUMBER Example". Available at: https://sqlsimplified.online/examples/top-n-per-group