How to Select the Row with the Highest Version ID for Each ID in MySQL?

MySQL: Controlling the Selection of Rows Returned by a Group

In MySQL, retrieving the row with the highest version for each ID can be a complex task. However, there are multiple approaches that can be employed to achieve this objective.

One method is to utilize a subquery:

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

This subquery sorts the rows in descending order by version ID for each ID. Subsequently, the main query groups the sorted rows by ID. This approach creates a temporary table in memory, but it can be efficient if an index exists on the (id, version_id) columns.

Another strategy involves a nested query:

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 calculates the maximum version ID for each ID in a subquery. The main query then joins the subquery with the original table using the ID and version_id columns to retrieve the rows with the maximum version IDs.

Both methods provide effective solutions for selecting the row with the highest version ID for each ID. The most appropriate approach depends on the specific requirements and performance considerations of the application.

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