Top-N per group query in SQL - Step-by-Step Execution

Concept Flow - Top-N per group query

Start with full table

↓

Partition data by group

↓

Order each group by criteria

↓

Assign row numbers within each group

↓

Filter rows where row number <= N

↓

Return top N rows per group

This flow shows how to get the top N rows for each group by partitioning, ordering, numbering, and filtering.

Execution Sample

SQL

SELECT department, employee, salary
FROM (
  SELECT department, employee, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
) sub
WHERE rn <= 2;

This query returns the top 2 highest salaries per department from the employees table.

Execution Table

Step	Row	department	employee	salary	ROW_NUMBER() rn	Action	Output Included
1	1	Sales	Alice	90000	1	Assign rn=1 for highest salary in Sales	Yes
2	2	Sales	Bob	85000	2	Assign rn=2 for second highest in Sales	Yes
3	3	Sales	Charlie	70000	3	Assign rn=3, excluded rn>2	No
4	4	HR	Diana	80000	1	Assign rn=1 for highest salary in HR	Yes
5	5	HR	Evan	75000	2	Assign rn=2 for second highest in HR	Yes
6	6	HR	Fiona	60000	3	Assign rn=3, excluded rn>2	No
7	7	IT	George	95000	1	Assign rn=1 for highest salary in IT	Yes
8	8	IT	Hannah	90000	2	Assign rn=2 for second highest in IT	Yes
9	9	IT	Ian	85000	3	Assign rn=3, excluded rn>2	No
10	Exit					All rows processed, filtered to rn <= 2	Query ends

💡 Rows with rn > 2 are excluded, so only top 2 salaries per department are returned.

Variable Tracker

Variable	Start	After Row 1	After Row 2	After Row 3	After Row 4	After Row 5	After Row 6	After Row 7	After Row 8	After Row 9	Final
rn (row number per group)	N/A	1 (Sales)	2 (Sales)	3 (Sales)	1 (HR)	2 (HR)	3 (HR)	1 (IT)	2 (IT)	3 (IT)	Final assigned per row

Key Moments - 2 Insights

Why do some rows have rn values greater than 2 but are not included in the output?

Rows with rn > 2 are filtered out by the WHERE clause (rn <= 2), so only the top 2 rows per group remain, as shown in execution_table rows 3, 6, and 9.

How does the ROW_NUMBER() function know to restart counting for each department?

Because of PARTITION BY department in the window function, ROW_NUMBER() resets to 1 for each new department group, as seen in execution_table steps 4 and 7.

Visual Quiz - 3 Questions

Look at the execution_table, what is the rn value for employee 'Evan' in the HR department?

A 1

B 3

C 2

D None

💡 Hint

Check execution_table row 5 under the 'ROW_NUMBER() rn' column.

At which step does the query exclude a row because rn > 2?

A Step 5

B Step 3

C Step 7

D Step 9

💡 Hint

Look for rows marked 'excluded rn>2' in the Action column in execution_table.

If we change rn <= 2 to rn <= 3 in the WHERE clause, how would the output change?

A More rows per group would be included

B Fewer rows per group would be included

C No change in output

D Query would error

💡 Hint

Refer to the exit_note and see which rows are currently excluded due to rn > 2.

Concept Snapshot

Top-N per group query syntax:
Use ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY criteria) to number rows per group.
Filter with WHERE rn <= N to get top N rows per group.
This returns the highest (or lowest) N rows per group based on ordering.
Commonly used for ranking or limiting results within groups.

Full Transcript

This visual execution trace shows how to write a SQL query to get the top N rows per group. The query uses ROW_NUMBER() with PARTITION BY to assign a row number to each row within its group, ordered by a criteria such as salary descending. Then it filters to keep only rows where the row number is less than or equal to N, effectively selecting the top N rows per group. The execution table walks through each row, showing the assigned row number and whether it is included in the output. The variable tracker shows how the row number resets for each group. Key moments clarify why some rows are excluded and how partitioning works. The quiz tests understanding of row numbers and filtering. The snapshot summarizes the syntax and behavior for quick reference.