How to Retrieve Only the Top N Rows per Group in MySQL?

MySQL efficiently obtains the first N rows of data for each group

Problem description:

In a database query, you may need to retrieve only the first N rows of data for each data grouping. For example, your dataset might contain multiple rows of information grouped by year and ID. If you want to display the top five highest rated records for each ID, you need to implement a method to limit the results to the desired number.

Solution:

In MySQL 8 or later, you can use the ROW_NUMBER, RANK, or DENSE_RANK functions to achieve this. The specific function you choose depends on the exact definition of "first N rows" and how to handle parallel situations. Here's a breakdown of the results generated by these functions:

• ROW_NUMBER: Assigns a sequence number to the rows within each group, starting from 1. This function is ideal if you want to break ties by giving rows with the same value the same rank.
• RANK: Assigns a ranking to the rows within each group, ties will result in the same ranking. This function is useful if you prioritize different rankings.
• DENSE_RANK: Similar to RANK, but it assigns dense rankings, meaning that even if there is a tie, there are no gaps between rankings. This function is suitable when you want to ensure that subsequent rows have a unique ranking.

Example:

The following query uses the ROW_NUMBER function to return the first 5 rows for each ID, sorted by rating in descending order:

SELECT
    year, id, rate
FROM (
    SELECT
        year, id, rate,
        ROW_NUMBER() OVER (PARTITION BY id ORDER BY rate DESC) AS rank_num
    FROM h
    WHERE year BETWEEN 2000 AND 2009
) AS subquery
WHERE rank_num <= 5;

Output:

year	id	rate
2006	p01	8.0
2003	p01	7.4
2008	p01	6.8
2001	p01	5.9
2007	p01	5.3
2001	p02	12.5
2004	p02	12.4
2002	p02	12.2
2003	p02	10.3
2000	p02	8.7

By using these functions you can effectively limit the results to the first N rows of each grouping and ensure the desired ordering of the data.

The above is the detailed content of How to Retrieve Only the Top N Rows per Group in MySQL?. For more information, please follow other related articles on the PHP Chinese website!