Home > Database > Mysql Tutorial > How to Efficiently Retrieve the Last Row for Each Group in MySQL?

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

DDD
Release: 2024-12-24 12:41:11
Original
171 people have browsed it

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;
Copy after login

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;
Copy after login

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;
Copy after login

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
Copy after login

Expected Results:

id, uid, value
3,   2, pickle
4,   1, world
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template