by
SQL Tutorial › Window Functions › Top N per Group

SQL Top N per Group

AdvancedLesson 49 of 6910 min read6 queries run on MySQL 8.4

“The top 3 products in each category“, “the latest order of each customer“, “the two best-paid people per department“: top-N-per-group is one of the most common real-world and interview SQL problems. LIMIT cannot do it, because it applies to the whole result. Window functions solve it in one clear pattern.

Advertisement
In this lesson
  • Solve top N per group with ROW_NUMBER in a CTE
  • Decide how ties should be handled (RANK vs ROW_NUMBER)
  • Get the latest or first row per group
  • Know the older correlated-subquery approach

Why LIMIT is not enough

SQL
SELECT dept_id, first_name, salaryFROM employeesORDER BY salary DESCLIMIT 2;
ResultMySQL 8.4
dept_idfirst_namesalary
NULLMeera250000
1Asha125000
2 rows in set

This returns the two best-paid people in the company, not two per department. We need the numbering to restart for each group.

The pattern

MySQL
WITH ranked AS (  SELECT t.*,         ROW_NUMBER() OVER (PARTITION BY group_col ORDER BY sort_col DESC) AS rn  FROM t)SELECT * FROM ranked WHERE rn <= N;

Top 2 earners per department

SQL
WITH ranked AS (  SELECT dept_id, first_name, salary,         ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, emp_id) AS rn  FROM employees  WHERE dept_id IS NOT NULL)SELECT dept_id, first_name, salary, rnFROM rankedWHERE rn <= 2ORDER BY dept_id, rn;
ResultMySQL 8.4
dept_idfirst_namesalaryrn
1Asha1250001
1Rahul980002
2Vikram1050001
2Neha540002
3Kavya880001
3Rohan450002
4Sneha780001
7 rows in set
How this query works, step by step
  1. PARTITION BY dept_id numbers each department separately.
  2. ORDER BY salary DESC, emp_id puts the highest salary first; emp_id breaks ties so the numbering is repeatable.
  3. The outer query keeps rows numbered 1 and 2 in each department.
  4. HR has only one employee, so it contributes one row.

Ties: ROW_NUMBER or RANK?

Top 3 salaries in Engineering. Priya and Karan tie for third place:

SQL
WITH ranked AS (  SELECT first_name, salary,         ROW_NUMBER() OVER (ORDER BY salary DESC, emp_id) AS rn,         RANK()       OVER (ORDER BY salary DESC)         AS rnk  FROM employees  WHERE dept_id = 1)SELECT first_name, salary, rn, rnk FROM ranked WHERE rnk <= 3;
ResultMySQL 8.4
first_namesalaryrnrnk
Asha12500011
Rahul9800022
Priya7200033
Karan7200043
4 rows in set
How this query works, step by step
  1. Filtering on rn <= 3 would return exactly three people and silently drop Karan.
  2. Filtering on rnk <= 3 returns four, because both tied people are third.
  3. Decide with the business which is right: “exactly N rows” (ROW_NUMBER) or “everyone in the top N positions” (RANK / DENSE_RANK).

Best-selling product per category

MySQL
WITH sales AS (  SELECT p.category, p.product_name, SUM(oi.quantity) AS units  FROM order_items oi  JOIN products p ON p.product_id = oi.product_id  GROUP BY p.category, p.product_name),ranked AS (  SELECT sales.*,         RANK() OVER (PARTITION BY category ORDER BY units DESC) AS rnk  FROM sales)SELECT category, product_name, unitsFROM rankedWHERE rnk = 1ORDER BY category;
ResultMySQL 8.4
categoryproduct_nameunits
ElectronicsMechanical Keyboard3
FurnitureOffice Chair3
FurnitureDesk Lamp3
StationeryNotebook Pack18
4 rows in set
How this query works, step by step
  1. sales totals the units sold per product.
  2. ranked ranks products inside each category by units.
  3. Keeping rnk = 1 returns each category’s best seller, and both products if two tie.

Latest order per customer

SQL
WITH ranked AS (  SELECT o.*,         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn  FROM orders o)SELECT customer_id, order_id, order_date, statusFROM rankedWHERE rn = 1ORDER BY customer_id;
ResultMySQL 8.4
customer_idorder_idorder_datestatus
110082025-04-28Pending
210072025-04-10Delivered
310092025-05-15Delivered
410052025-03-08Delivered
510102025-06-01Delivered
5 rows in set

The pre-MySQL 8 way

Without window functions, top-1 per group used a correlated subquery or a join to the group maximum (Correlated Subqueries). Top N > 1 needed a counting subquery:

SQL
SELECT e.dept_id, e.first_name, e.salaryFROM employees eWHERE (SELECT COUNT(*) FROM employees x       WHERE x.dept_id = e.dept_id AND x.salary > e.salary) < 2  AND e.dept_id IS NOT NULLORDER BY e.dept_id, e.salary DESC;
ResultMySQL 8.4
dept_idfirst_namesalary
1Asha125000
1Rahul98000
2Vikram105000
2Neha54000
3Kavya88000
3Rohan45000
4Sneha78000
7 rows in set
How this query works, step by step
  1. For each employee, count colleagues in the same department who earn more.
  2. Fewer than 2 such colleagues means the employee is in the top 2.
  3. It works, but it is harder to read and slower than the window-function pattern.

Exercise: Two cheapest products per category

List the two cheapest products of each category.

Expected resultMySQL 8.4
categoryproduct_nameprice
ElectronicsWireless Mouse799
ElectronicsUSB-C Hub1999
FurnitureDesk Lamp1499
FurnitureMonitor Arm2799
StationeryGel Pens199
StationeryNotebook Pack249
6 rows in set
Hint 1

ROW_NUMBER() OVER (PARTITION BY category ORDER BY price) and keep rn <= 2.

Quick quiz

1. Why can’t ORDER BY ... LIMIT 3 give the top 3 per group?

2. Which function returns everyone tied in the top N positions?

3. Why add emp_id to the window’s ORDER BY?

Top N per group FAQ

How do I select the top N rows per group in SQL?

Number rows within each group with ROW_NUMBER() OVER (PARTITION BY group ORDER BY value DESC) in a CTE, then keep the rows where the number is at most N.

How do I get the latest record for each group?

Use ROW_NUMBER() OVER (PARTITION BY group ORDER BY date DESC) and keep row number 1.

How do I handle ties in top N queries?

Use ROW_NUMBER with a tie-breaker for exactly N rows, or RANK / DENSE_RANK to include every row tied at the cut-off.