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

Employees & Departments

Loading database engine...

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:

Movies (Movies/Actors/Ratings)

Loading database engine...

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.