How to Select the Group-Wise Maximum in MySQL?

Selecting the Group-Wise Maximum in MySQL

In relational databases, sometimes you need to retrieve the row with the maximum value of a specific column for each group. This is known as selecting the group-wise maximum. In MySQL, there are several approaches to accomplish this.

Subquery Method

One efficient method is to use a subquery:

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

This subquery creates a table of the maximum version IDs for each ID and then joins it with the original table to retrieve the corresponding rows. It is relatively efficient and assumes the existence of an index on the (id, version_id) columns.

Aggregation and Ranking Methods

Alternatively, you can use aggregation and ranking functions:

SELECT id,
       version_id,
       field1,
       field2
FROM (
  SELECT id,
          version_id,
          field1,
          field2,
          RANK() OVER (PARTITION BY id ORDER BY version_id DESC) AS ranking
  FROM table
) AS ranked_table
WHERE ranking = 1;

This query ranks the rows for each ID in descending order of version_id and selects those with a ranking of 1, effectively filtering for the maximum version.

Window Functions

Window functions can also be used for this purpose:

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

This query calculates the maximum version ID for each ID using the MAX() over() window function and selects the rows with matching version IDs.

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