How to select last record from each day with MySQL

max, mysql

Solution

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.

Problem

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

Original source