Method for obtaining the last record of each userid In the MySQL table, mysqluserid

Method for obtaining the last record of each userid In the MySQL table, mysqluserid

See the following table:

CREATE TABLE `t1` (`userid` int(11) DEFAULT NULL,`atime` datetime DEFAULT NULL,KEY `idx_userid` (`userid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8； CREATE TABLE `t1` (`userid` int(11) DEFAULT NULL,`atime` datetime DEFAULT NULL,KEY `idx_userid` (`userid`)) ENGINE=InnoDB DEFAULT CHARSET=utf8；

The data is as follows:

MySQL> select * from t1;+--------+---------------------+| userid | atime |+--------+---------------------+| 1 | 2013-08-12 11:05:25 || 2 | 2013-08-12 11:05:29 || 3 | 2013-08-12 11:05:32 || 5 | 2013-08-12 11:05:34 || 1 | 2013-08-12 11:05:40 || 2 | 2013-08-12 11:05:43 || 3 | 2013-08-12 11:05:48 || 5 | 2013-08-12 11:06:03 |+--------+---------------------+8 rows in set (0.00 sec) MySQL> select * from t1;+--------+---------------------+| userid | atime |+--------+---------------------+| 1 | 2013-08-12 11:05:25 || 2 | 2013-08-12 11:05:29 || 3 | 2013-08-12 11:05:32 || 5 | 2013-08-12 11:05:34 || 1 | 2013-08-12 11:05:40 || 2 | 2013-08-12 11:05:43 || 3 | 2013-08-12 11:05:48 || 5 | 2013-08-12 11:06:03 |+--------+---------------------+8 rows in set (0.00 sec)

The userid is not unique. It is required that the time corresponding to each userid In the table be the closest to the current time. when we first see this condition, we usually think of borrowing a temporary table and adding a primary table using the join operation.
Give a simple method:

MySQL> select userid,substring_index(group_concat(atime order by atime desc),",",1) as atime from t1 group by userid;+--------+---------------------+| userid | atime |+--------+---------------------+| 1 | 2013-08-12 11:05:40 || 2 | 2013-08-12 11:05:43 || 3 | 2013-08-12 11:05:48 || 5 | 2013-08-12 11:06:03 |+--------+---------------------+4 rows in set (0.03 sec) MySQL> select userid,substring_index(group_concat(atime order by atime desc),",",1) as atime from t1 group by userid;+--------+---------------------+| userid | atime |+--------+---------------------+| 1 | 2013-08-12 11:05:40 || 2 | 2013-08-12 11:05:43 || 3 | 2013-08-12 11:05:48 || 5 | 2013-08-12 11:06:03 |+--------+---------------------+4 rows in set (0.03 sec)

Good luck!