mysql record sorting issue

Question

i have this table structure

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

i want to select one record for each agentid where datetime is sorted in ascending order for example agentid 6 with datetime 2016-06-17 12:55:48 should be selected and for agentid 7 datetime 2016-06-17 12:55:54 should be selected

is it possible to do it with one query?

i know the query doesnt make sense but just to explain what i want to do

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

@Strawberry, as the edit you made, how did you convert image to text ? — kamal pal, Jun 18 '16 at 09:57

score 3 · Accepted Answer · answered Jun 18 '16 at 09:50

3

You could use MIN function, which will fetch you the minimum datetime for each agentid.

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

answered Jun 18 '16 at 09:50

kamal pal

4,187
5
25
40

1

thanks it worked :) – Wasif Khalil Jun 18 '16 at 09:53

mysql record sorting issue

1 Answers1