I have a query which has a
`users`.`id` as `user_id`,
DATE(records.created_at) AS date,
TIME(MIN(records.created_at)) AS first_log,
TIME(MAX(records.created_at)) AS last_log
inner join `users` on `records`.`din` = `users`.`din`
where records.created_at BETWEEN '2016-09-05 00:00:00'
AND '2016-09-08 00:00:00'
group by `users`.`username`, DATE(records.created_at)
order by `first_log` asc
group_concat() trick might do exactly what you want:
select substring_index(group_concat(r.dn order by created_at desc), ',', 1) as last_dn, u.username, u.id as `user_id`, DATE(r.created_at) AS date, TIME(MIN(r.created_at)) AS first_log, TIME(MAX(r.created_at)) AS last_log from records r inner join users u on r.din = u.din where r.created_at >= '2016-09-05' and r.created_at < '2016-09-08' group by u.username, DATE(r.created_at) order by first_log asc;
As written, this has three limitations:
dndoesn't have a comma in it (that is easily fixed by using a different separator).
dns for a user for a day won't exceed the maximum length of a string for
group_concat(). That can also be adjusted using a system parameter.
last_dnis an string, even if that is not the original type.
But this trick works well in most cases. The parameter
group_concat_max_len is the size of the intermediate string. Its default (described here) is 1,024. This should be fine for up to a hundred or more integers. It is a session parameter and easily changed.
Also note the following changes:
between, because it is inclusive. I am guessing that you want date/time values strictly before the 8th.