MySQL - Select Top N Rows Per Group

It is a very common task to select top N rows from each group in MySQL when doing data analysis and reporting. You might use this to get the top 3 selling products for each product category, get the highest paid employee in each department, find out the top 10 popular posts in your blog, and so on. We will go over modern SQL techniques of answering top N rows per group questions using Row_Number, Rank, and Dense_Rank window functions.

Example Data

---

A school has collected student score data for Math and English classes. We would like to perform analysis of top student scores in each class.

create table student_score (
	class_name VARCHAR(9),
	student_login VARCHAR(50),
	score INT
);

insert into student_score (class_name, student_login, score) values ('math', 'gacreman0', 100);
insert into student_score (class_name, student_login, score) values ('english', 'ewoodington1', 87);
insert into student_score (class_name, student_login, score) values ('math', 'ctilliard2', 72);
insert into student_score (class_name, student_login, score) values ('english', 'gzorer3', 81);
insert into student_score (class_name, student_login, score) values ('english', 'swyre4', 75);
insert into student_score (class_name, student_login, score) values ('math', 'ppadgett8', 82);
insert into student_score (class_name, student_login, score) values ('math', 'mreynalds9', 100);
insert into student_score (class_name, student_login, score) values ('math', 'dlettena', 100);
insert into student_score (class_name, student_login, score) values ('math', 'dbartellib', 86);
insert into student_score (class_name, student_login, score) values ('english', 'tlorinezc', 87);
insert into student_score (class_name, student_login, score) values ('english', 'ddeftied', 81);

Use

DB-Fiddle to execute SQL scripts on sample data.

Select Top N Rows Per Group with Row_Number

---

If the business requirements are to always return exactly 3 top students in each class, Row_Number window function should be used. When there are tied scores, sort student_login ascendingly to determine which student_login to pick. Since the PARTITION BY clause is present, the rankings are reset for rows in each class.

SELECT *
	,row_number() OVER (
		PARTITION BY class_name ORDER BY score DESC
			,student_login ASC
		) AS row_num
FROM student_score

Here is the completed script to get exactly 3 top students in each class with Row_Number.

WITH score_analysis
AS (
	SELECT *,
		row_number() OVER (
			PARTITION BY class_name ORDER BY score DESC,
				student_login ASC
			) AS row_num
	FROM student_score
	)
SELECT class_name,
	student_login,
	score
FROM score_analysis
WHERE row_num <= 3;

Here is the output of the query.

class_name	student_login	score
english	ewoodington1	87
english	tlorinezc	87
english	ddeftied	81
math	dlettena	100
math	gacreman0	100
math	mreynalds9	100

Select Top N Rows Per Group with Dense_Rank

---

If the business requirements are to always report a first, second, and third place for each class, regardless of score ties for each place, Dense_Rank window function is required to achieve this. Each place could have 1 or multiple students.

If two or more rows tie, there will be no gap in the sequence of ranked values. Since the PARTITION BY clause is present, the rankings are reset for rows in each class.

SELECT *
	,dense_rank() OVER (
		PARTITION BY class_name ORDER BY score DESC
		) AS dense_rank_num
FROM student_score

Here is the completed script to always have first, second, and third place students in each class with Dense_Rank.

WITH score_analysis
AS (
	SELECT *,
		dense_rank() OVER (
			PARTITION BY class_name ORDER BY score DESC
			) AS dense_rank_num
	FROM student_score
	)
SELECT class_name,
	student_login,
	score
FROM score_analysis
WHERE dense_rank_num <= 3;

Here is the output of the query.

class_name	student_login	score
english	ewoodington1	87
english	tlorinezc	87
english	gzorer3	81
english	ddeftied	81
english	swyre4	75
math	gacreman0	100
math	mreynalds9	100
math	dlettena	100
math	dbartellib	86
math	ppadgett8	82

Select Top N Rows Per Group with Rank

---

If the business requirements are to allow the possibility of no second or third place, Rank function can be used to achieve this. Each place could have 0, 1, or multiple students.

Rows with equal scores for the ranking criteria receive the same rank. The next rank is calculated by adding the number of tied rows to the tied rank. Therefore, Rank function will leave gaps in the ranking values. Since the PARTITION BY clause is present, the rankings are reset for rows in each class.

SELECT *
	,rank() OVER (
		PARTITION BY class_name ORDER BY score DESC
		) AS rank_num
FROM student_score

Example 1: English class has 2 scores tied at first place so there is no second place. But there is a third place with 2 scores tied.

Example 2: Math class has 3 scores tied at first place, there will be no second or third place.

Here is the completed script to always have first, second, and third place students in each class with Dense_Rank.

WITH score_analysis
AS (
	SELECT *,
		rank() OVER (
			PARTITION BY class_name ORDER BY score DESC
			) AS rank_num
	FROM student_score
	)
SELECT class_name,
	student_login,
	score
FROM score_analysis
WHERE rank_num <= 3;

Here is the output of the query.

class_name	student_login	score
english	ewoodington1	87
english	tlorinezc	87
english	gzorer3	81
english	ddeftied	81
math	gacreman0	100
math	mreynalds9	100
math	dlettena	100

Conclusion

---

Hopefully through the examples discussed above, you can see that when a tie of two or more scores occurs, both Rank and Dense_Rank assign the same rank number to all records with the same value.

Where they differ is that Rank function continues the rank value in line with the Row_Number series so it skips the next available ranking value. This results in gaps in the ranking values.

Dense_Rank will instead use the next chronological ranking value so it leaves no gaps in the ranking values.

As you can see that Row_Number, Rank, and Dense_Rank window functions are very useful in analyzing data and answering Top N Rows Per Group types of questions. It's good to have a good understanding on each of them so you can determine which one to use based on the business requirements.