How to Efficiently Retrieve the Last Row per Group in MySQL?

Retrieving the 'Last' Row per Group with MySQL

Problem:

Find a more efficient method to retrieve the last row for each group in a MySQL table, using a query similar to:

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

Potential Solution:

Consider this alternative query:

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

Explanation:

This query Left Joins each row in the table with its successor based on the ID column. If no successor exists (i.e., it's the last row in a group), the t2.id column will be NULL. By filtering for NULLs, we effectively isolate the last rows of each group.

Additional Notes:

Use EXPLAIN to analyze the query performance of the original query and the alternative solution.

This method is especially useful for large datasets and complex groupings. For simpler cases, other approaches may be more efficient.

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