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

Return the Last Row of Each Group in MySQL Effectively

Returning the last row of each group in MySQL is often necessary for data manipulation. The query provided in the prompt:

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

achieves this by performing a nested query to find the maximum ID for each unique UID, then selecting only the rows with the matching ID.

A More Efficient Approach

However, a more efficient query can be used to eliminate the nested query:

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

This query uses a JOIN operation to directly match the maximum ID for each UID, providing improved performance compared to the nested query approach.

Additional Optimization Considerations

For further optimization, EXPLAIN can be used to analyze the query plan and identify any potential bottlenecks. Additionally, an index on the UID column can significantly enhance query performance.

Alternative Approach

Another option is to use the LAG() function:

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

This query uses the LAG() function to identify the next row in the group. If there is no subsequent row (i.e., the current row is the last in its group), the query will return the current row. While this approach can be efficient, it may not always be as performant as the JOIN-based queries discussed above.

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