Top-N per group query in SQL - Cheat Sheet & Quick Revision

Recall & Review

beginner

What is a Top-N per group query in SQL?

It is a query that finds the top N rows for each group in a table, like the top 3 salespeople per region.

beginner

Which SQL function is commonly used to assign ranks for Top-N per group queries?

The ROW_NUMBER() function assigns a unique rank number to each row within a group, helping to select the top N rows.

intermediate

How do you limit results to the top 3 rows per group using ROW_NUMBER()?

Use ROW_NUMBER() OVER (PARTITION BY group_column ORDER BY sort_column DESC) and then filter WHERE row_number <= 3.

intermediate

Why is PARTITION BY important in Top-N per group queries?

PARTITION BY divides the data into groups so the ranking function restarts for each group, allowing top N per group selection.

intermediate

Can you use LIMIT or FETCH FIRST directly to get Top-N per group?

No, LIMIT or FETCH FIRST applies to the whole result set, not per group. You need ranking functions like ROW_NUMBER() for per group limits.

Which SQL clause is used to restart row numbering for each group in a Top-N per group query?

A GROUP BY

B PARTITION BY

C ORDER BY

D WHERE

PARTITION BY divides rows into groups so ROW_NUMBER() restarts numbering for each group.

What does ROW_NUMBER() do in a Top-N per group query?

A Assigns a unique rank number to each row within its group

B Counts total rows in the table

C Filters rows based on a condition

D Groups rows by a column

ROW_NUMBER() assigns a unique rank to each row within each group, useful for selecting top N.

How do you select the top 5 rows per group after using ROW_NUMBER()?

A LIMIT 5

B GROUP BY row_number

C WHERE row_number <= 5

D ORDER BY row_number DESC

Filtering with WHERE row_number <= 5 keeps only the top 5 rows per group.

Which of these is NOT a correct way to get Top-N per group in SQL?

A Using LIMIT without partitioning

B Using RANK() with PARTITION BY

C Using DENSE_RANK() with PARTITION BY

D Using ROW_NUMBER() with PARTITION BY

LIMIT alone does not work per group; it limits the whole result set.

What is the purpose of ORDER BY inside ROW_NUMBER() OVER()?

A To limit the number of rows returned

B To group rows by a column

C To filter rows after ranking

D To define how rows are ranked within each group

ORDER BY inside ROW_NUMBER() defines the ranking order within each group.

Explain how to write a SQL query to get the top 3 highest salaries per department.

Think about dividing data by department and ranking salaries.

Describe why LIMIT alone cannot be used to get Top-N per group in SQL.

Consider how LIMIT works on the entire query output.