How to select last record from each day with MySQL

Question

I know there are already some solutions to this question but they don't seem to be working for me.

I want to select the last record from each day, per user, on my database. I have a database that stores a user's location by country and date down to the second.

The select query I'm trying to run is:

SELECT MAX(date), id, country FROM dk_location_records WHERE userid = '10'
AND (date > '2012-04-06 00:00:00' AND date < '2012-05-08 23:59:59')
AND active = '1' GROUP BY DATE(date) ORDER BY date ASC

However, what this actually does is show me the last date from each day, but not the correctly corresponding id and country. It actually gets the first id and country, alongside the last date.

What am I doing wrong?

Help greatly appreciated!

Thanks,

Jack

Answer 1

Try this query to show last record from each day, per user, -

SELECT t1.* FROM dk_location_records t1
  JOIN (SELECT DATE(date) date_date, userid, MAX(date) max_date
        FROM dk_location_records
        GROUP BY date_date, userid
        ) t2
    ON t1.date = t2.max_date AND t1.userid = t2.userid;

...add your WHERE conditions if you need.