Mysql obtains the latest group data

The source data is as follows: you need to obtain the latest data based on the target_id group, that is, there are two solutions: Solution 1: you can also find the largest data ID through the same table subquery or joint query: {code ...} solution 2: query in two steps. in php, first query the maximum ID, and then query the number of IDs...

The source data is as follows:

The result is that the latest data is obtained based on the target_id group:

Currently, we have two solutions:
Solution 1: find the largest data ID through the same table subquery or joint query

There is also a way to write:

select * from (select * from track     where type='task' and target_id in(...) ORDER BY time DESC) as temp GROUP BY  target_id

Solution 2: query in two steps. php first queries the maximum ID, and then queries the list data through the ID array.

Is there any other simple way to solve this problem?
This requirement should be quite common!

===== Attaching structure and data ====

Drop table if exists 'track'; create table 'track' ('id' int (11) not null AUTO_INCREMENT, 'type' varchar (50) not null default ''comment' task => task follow-up, project => project follow-up ', 'target _ id' int (11) DEFAULT '0' comment' follow-up target ID ', 'user _ id' int (11) DEFAULT '0' comment' follow-up user', 'User _ name' varchar (100) DEFAULT ''comment' follow-up username ', 'content' varchar (500) DEFAULT ''comment' follow-up content', 'Time' int (11) DEFAULT '0' comment' follow-up time ', primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 10 default charset = utf8 COMMENT = 'follow-up record table '; -- ---------------------------- Records of track -- ---------------------------- insert into 'track' VALUES ('1', 'task', '67', '1', 'super admin', 'doesn't matter... ', '200'); insert into 'track' VALUES ('2', 'task', '67', '1', 'super admin', 'tttttt ', '20140901'); insert into 'track' VALUES ('7', 'task', '67', '1', 'super admin', 'consumption only ', '123'); insert into 'track' VALUES ('8', 'task', '34', '1', 'super admin', 'sts ', '123'); insert into 'track' VALUES ('9', 'task', '34', '1', 'super admin', 'ceilings ', '123 ');

Reply content:

The source data is as follows:

The result is that the latest data is obtained based on the target_id group:

Currently, we have two solutions:
Solution 1: find the largest data ID through the same table subquery or joint query

There is also a way to write:

select * from (select * from track     where type='task' and target_id in(...) ORDER BY time DESC) as temp GROUP BY  target_id

Solution 2: query in two steps. php first queries the maximum ID, and then queries the list data through the ID array.

Is there any other simple way to solve this problem?
This requirement should be quite common!

===== Attaching structure and data ====

Drop table if exists 'track'; create table 'track' ('id' int (11) not null AUTO_INCREMENT, 'type' varchar (50) not null default ''comment' task => task follow-up, project => project follow-up ', 'target _ id' int (11) DEFAULT '0' comment' follow-up target ID ', 'user _ id' int (11) DEFAULT '0' comment' follow-up user', 'User _ name' varchar (100) DEFAULT ''comment' follow-up username ', 'content' varchar (500) DEFAULT ''comment' follow-up content', 'Time' int (11) DEFAULT '0' comment' follow-up time ', primary key ('id') ENGINE = InnoDB AUTO_INCREMENT = 10 default charset = utf8 COMMENT = 'follow-up record table '; -- ---------------------------- Records of track -- ---------------------------- insert into 'track' VALUES ('1', 'task', '67', '1', 'super admin', 'doesn't matter... ', '200'); insert into 'track' VALUES ('2', 'task', '67', '1', 'super admin', 'tttttt ', '20140901'); insert into 'track' VALUES ('7', 'task', '67', '1', 'super admin', 'consumption only ', '123'); insert into 'track' VALUES ('8', 'task', '34', '1', 'super admin', 'sts ', '123'); insert into 'track' VALUES ('9', 'task', '34', '1', 'super admin', 'ceilings ', '123 ');

Place the data with the largest id in the temporary table

CREATE TEMPORARY TABLE tmp_id(`id` int(11) not null,PRIMARY KEY (`id`) )

Then

INSERT INTO tmp_id SELECT max(`id`) as id FROM track GROUP BY target_id

Then join.
We recommend that you delete the temporary table explicitly.

DROP TEMPORARY TABLE IF EXISTS tmp_id

I think the scheme 1A is quite good, but why do I need to write the in condition for target_id? the same result is true if I don't write it.

Generally, solution 1 is used, and solution 1 is written in another way.

select * from track where id in(select substring_index(group_concat(id order by id desc),',',1) as maxid  from track group by target_id);