How to Efficiently Retrieve the Last Row of Each Group in MySQL?

Returning the 'Last' Row of Each 'Group By' in MySQL

Efficiently retrieving the last row of each grouped set in MySQL is a common task. A previous approach proposed a query using a subquery within the WHERE clause:

select * 
    from foo as a
    where a.id = (select max(id) from foo where uid = a.uid group by uid)
    group by uid;

However, an alternative solution may offer improved performance:

SELECT t1.* FROM foo t1
  JOIN (SELECT uid, MAX(id) id FROM foo GROUP BY uid) t2
    ON t1.id = t2.id AND t1.uid = t2.uid;

This query utilizes a JOIN operation to explicitly join the original table ('foo') with a derived table that contains the maximum ID value for each unique user ID. The ON clause ensures that only the rows with matching IDs and user IDs are joined.

To further optimize the query, it's recommended to create an index on the 'uid' column, as it is used for grouping and joining. Utilizing EXPLAIN can provide insights into the query's execution plan and identify potential performance bottlenecks.

Another approach, using the LEFT JOIN operator, can also achieve the desired result:

SELECT t1.* FROM foo t1
  LEFT JOIN foo t2
    ON t1.id < t2.id AND t1.uid = t2.uid
WHERE t2.id is NULL;

In this query, the LEFT JOIN performs an outer join, matching rows from 'foo' based on 'uid' and 'id' criteria. The WHERE clause ensures that only the rows without a subsequent row in the same group are selected, effectively returning the last row for each user ID.

By considering alternative approaches and optimizing the query through indexing and EXPLAIN analysis, it's possible to achieve efficient retrieval of the last row for each 'group by' in MySQL.

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