How to Select the Row with the Maximum Value from Each Group in MySQL?

Grouping and Retrieving Rows Based on Maximum Value in MySQL

In database management, retrieving specific rows from a group can be crucial for data analysis and reporting. In MySQL, grouping and ordering operations play a vital role in this regard. However, a common challenge arises when trying to select the row with the highest value from a group, after applying a group by operation.

Consider a scenario where you have a table with rows representing multiple versions of data, identified by an id and a version_id column. The goal is to retrieve the row with the highest version_id for each unique id.

While the intuitive approach of using group by with an order by clause fails to provide the desired result, MySQL offers several efficient solutions for this query:

Max-Based Grouping:

SELECT *
FROM (SELECT id, MAX(version_id) AS max_version_id FROM table GROUP BY id) AS subquery
JOIN table ON subquery.id = table.id AND subquery.max_version_id = table.version_id;

This query leverages a subquery to calculate the maximum version_id for each id group. The main query then performs an inner join to retrieve the corresponding rows from the original table.

Correlated Subquery:

SELECT t1.*
FROM table AS t1
WHERE t1.version_id = (SELECT MAX(version_id) FROM table WHERE id = t1.id);

This approach uses a correlated subquery to compare each row's version_id with the maximum value within its id group. The main query selects only the rows that satisfy the condition.

Window Function:

SELECT id, MAX(version_id) OVER (PARTITION BY id) AS max_version_id, field1, field2
FROM table
ORDER BY id;

Window functions provide an alternative approach by calculating the maximum version_id within each id partition. The OVER clause specifies the partitioning criteria, and the ORDER BY clause ensures that the rows are ordered within each partition.

These solutions offer efficient ways to retrieve the rows with the highest version_id for each distinct id. By understanding the nuances of grouping and ordering operations, database users can refine their queries to extract meaningful data from complex data structures.

The above is the detailed content of How to Select the Row with the Maximum Value from Each Group in MySQL?. For more information, please follow other related articles on the PHP Chinese website!