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

Retrieving the Last Row for Each Group in MySQL

To gather the 'last' row of each group using 'group by' in MySQL, consider the following efficient approaches:

Subquery Method:

One common approach is using a subquery:

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

Join Method:

An alternative is using a join:

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;

LEFT JOIN Method:

For a more nuanced approach:

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

Comparing Queries

You can analyze the efficiency of these queries using EXPLAIN statements.

Table Structure and Data:

CREATE TABLE foo (
    id INT(10) NOT NULL AUTO_INCREMENT,
    uid INT(10) NOT NULL,
    value VARCHAR(50) NOT NULL,
    PRIMARY KEY (`id`),
    INDEX `uid` (`uid`)
);

id, uid, value
1,   1, hello
2,   2, cheese
3,   2, pickle
4,   1, world

Expected Results:

id, uid, value
3,   2, pickle
4,   1, world

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