Wasif Khalil Wasif Khalil - 5 months ago 11
MySQL Question

mysql record sorting issue

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

Answer

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