Mysql gets grouped latest data

The source data is as follows:

The required effect is to obtain the latest data based on target_id grouping, which is:

There are currently two options in mind:
Option 1: Find the largest data ID through subqueries or joins in the same table

There is another way to write it:

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

Option 2: Query in two steps, first query the maximum ID in php, and then query the list data through the ID array

What I want to ask is is there any other simpler way to deal with this problem?
This kind of demand should be relatively common!

=====Attach structure and data=====

<code>DROP TABLE IF EXISTS `track`;
CREATE TABLE `track` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(50) NOT NULL DEFAULT '' COMMENT 'task => 任务跟进,project => 项目跟进 ',
  `target_id` int(11) DEFAULT '0' COMMENT '跟进目标ID',
  `user_id` int(11) DEFAULT '0' COMMENT '跟进用户',
  `user_name` varchar(100) DEFAULT '' COMMENT '跟进用户名称',
  `content` varchar(500) DEFAULT '' COMMENT '跟进内容',
  `time` int(11) DEFAULT '0' COMMENT '跟进时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='跟进记录表';

-- ----------------------------
-- Records of track
-- ----------------------------
INSERT INTO `track` VALUES ('1', 'task', '67', '1', '超级管理员', '无所谓...', '1467774850');
INSERT INTO `track` VALUES ('2', 'task', '67', '1', '超级管理员', 'TTTT', '1467777620');
INSERT INTO `track` VALUES ('7', 'task', '67', '1', '超级管理员', '只耗损', '1468288894');
INSERT INTO `track` VALUES ('8', 'task', '34', '1', '超级管理员', 'STS', '1468288917');
INSERT INTO `track` VALUES ('9', 'task', '34', '1', '超级管理员', '吊顶', '1468288954');</code>

Reply content:

The source data is as follows:

The required effect is to obtain the latest data based on target_id grouping, which is:

There are currently two options in mind:
Option 1: Find the largest data ID through subqueries or joins in the same table

There is another way to write it:

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

Option 2: Query in two steps, first query the maximum ID in php, and then query the list data through the ID array

What I want to ask is is there any other simpler way to deal with this problem?
This kind of demand should be relatively common!

=====Attach structure and data=====

<code>DROP TABLE IF EXISTS `track`;
CREATE TABLE `track` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `type` varchar(50) NOT NULL DEFAULT '' COMMENT 'task => 任务跟进,project => 项目跟进 ',
  `target_id` int(11) DEFAULT '0' COMMENT '跟进目标ID',
  `user_id` int(11) DEFAULT '0' COMMENT '跟进用户',
  `user_name` varchar(100) DEFAULT '' COMMENT '跟进用户名称',
  `content` varchar(500) DEFAULT '' COMMENT '跟进内容',
  `time` int(11) DEFAULT '0' COMMENT '跟进时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=10 DEFAULT CHARSET=utf8 COMMENT='跟进记录表';

-- ----------------------------
-- Records of track
-- ----------------------------
INSERT INTO `track` VALUES ('1', 'task', '67', '1', '超级管理员', '无所谓...', '1467774850');
INSERT INTO `track` VALUES ('2', 'task', '67', '1', '超级管理员', 'TTTT', '1467777620');
INSERT INTO `track` VALUES ('7', 'task', '67', '1', '超级管理员', '只耗损', '1468288894');
INSERT INTO `track` VALUES ('8', 'task', '34', '1', '超级管理员', 'STS', '1468288917');
INSERT INTO `track` VALUES ('9', 'task', '34', '1', '超级管理员', '吊顶', '1468288954');</code>

Put the data with the largest ID into the temporary table

<code>CREATE TEMPORARY TABLE tmp_id(`id` int(11) not null,PRIMARY KEY (`id`) )
</code>

Then

<code>INSERT INTO tmp_id SELECT max(`id`) as id FROM track GROUP BY target_id
</code>

Then just join it
Finally, it is recommended to delete the temporary table explicitly

<code>DROP TEMPORARY TABLE IF EXISTS tmp_id</code>

I think plan 1A is pretty good, but why do we need to write in condition for target_id? If we don’t write it, the same result will be the same.

Generally, option 1 is used. There is another way to write option 1

<code>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);</code>