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

MySQL: Controlling Which Row is Returned in a Group BY Query

In MySQL, retrieving the row with the highest value from each group is a common task. However, using the standard GROUP BY clause with ORDER BY can sometimes lead to unexpected results.

To achieve this, we can employ alternative approaches that override the default grouping behavior:

Subquery Method:

This method involves creating a subquery to obtain the maximum version_id for each unique id:

SELECT *
FROM (SELECT id, MAX(version_id) AS version_id FROM table GROUP BY id) t1
INNER JOIN table t2 ON (t2.id = t1.id AND t1.version_id = t2.version_id)

This query joins the original table with a subquery that selects the maximum version_id for each id. The join ensures that only the rows with the highest version_id are returned.

Subquery with ORDER BY Trick:

A variation of the subquery method involves using ORDER BY in the subquery to ensure specific ordering:

SELECT *
FROM (SELECT * FROM table ORDER BY version_id DESC) t1
GROUP BY t1.id

This query effectively sorts the rows in descending order of version_id within each group before performing the grouping, ensuring that the row with the highest version_id is retained for each group.

Named Windows Function:

Window functions allow calculations to be performed within a set of rows defined by a window, which can be used for this task:

SELECT *
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY id ORDER BY version_id DESC) AS rn
  FROM table
) t
WHERE rn = 1

This query calculates the row number for each unique id within the partition defined by ROW_NUMBER. The WHERE clause filters out rows with row numbers other than 1, ensuring that only the row with the highest version_id for each id is returned.

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