php - mysql记录排序问题

This question already has answers here:

How to select the most recent set of dated records from a mysql table
（7个答案）
三年前关闭。
我有这个桌子结构

leadid agentid datetime            ip
     1       6 2016-06-17 12:55:48 127.0.0.1
     5       6 2016-06-17 12:56:26 127.0.0.1
     9       6 2016-06-17 12:58:18 127.0.0.].
    13       6 2016-06-17 12:58:19 127.0.0.1
    17       6 2016-06-17 12:58:20 127.0.0.1
     2       7 2016-06-17 12:55:54 127.0.0.1
     6       7 2016-06-17 12:56:32 127.0.0.1
    10       7 2016-06-17 12:58:18 127.0.0.1
    14       7 2016-06-17 12:58:19 127.0.0.1
    18       7 2016-06-17 12:58:20 127.0.0.1
     3       8 2016-06-17 12:55:56 127.0.0.].
     7       8 2016-06-17 12:58:18 127.0.0.1
    11       8 2016-06-17 12:58:19 127.0.0.1
    15       8 2016-06-17 12:58:20 127.0.0.1
    19       8 2016-06-17 12:58:21 127.0.0.1
     4       9 2016-06-17 12:56:22 127.0.0.1
     8       9 2016-06-17 12:58:18 127.0.0.1
    12       9 2016-06-17 12:58:19 127.0.0.1
    16       9 2016-06-17 12:58:20 127.0.0.1
    20       9 2016-06-17 12:58:21 127.0.0.1

我想为每个agentid选择一条记录，其中datetime按升序排序
例如，应选择日期为2016-06-17 12:55:48的agentid 6，而对于agentid 7，应选择日期为2016-06-17 12:55:54
一个查询就可以做到吗？
我知道这个问题没有意义，只是为了解释我想做什么

SELECT COUNT(agentid)
     , `agentid`
     , `datetime`
  FROM leadassignment
 GROUP
    BY agentid
 ORDER
    BY datetime ASC

Expression #3 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'timeshareleads.leadassignment.datetime' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

最佳答案

您可以使用MIN函数，它将为每个agentid获取最短的日期时间。

SELECT count(agentid), `agentid`, min(`datetime`) as 'datetime'
FROM leadassignment
GROUP BY agentid