How to Get the Top N Rows for Each Group in MySQL?

Extracting Top N Rows for Each Group in MySQL

Need to fetch the top N rows for every group in your MySQL data? Standard ordering might not always give you the results you expect. This guide shows how to use MySQL's powerful window functions, like ROW_NUMBER, RANK, and DENSE_RANK, to achieve this efficiently.

Imagine a table with PKID (Primary Key), CATID (category), and value columns. To get the top 5 rows for each CATID, use the ROW_NUMBER function:

<code class="language-sql">SELECT PKID, CATID, value,
ROW_NUMBER() OVER (PARTITION BY CATID ORDER BY value DESC) AS RowNum
FROM table_name;</code>

ROW_NUMBER assigns a unique rank within each CATID group, ordered by value (descending in this case). To filter for only the top 5, wrap this in a subquery:

<code class="language-sql">SELECT PKID, CATID, value
FROM (
  SELECT PKID, CATID, value,
  ROW_NUMBER() OVER (PARTITION BY CATID ORDER BY value DESC) AS RowNum
  FROM table_name
) AS subquery
WHERE RowNum <= 5;</code>

This refined query efficiently retrieves only the top 5 rows per category, providing a precise and optimized solution for your data retrieval needs. Remember to adjust the <= 5 condition to control the number of top rows you want per group.

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