How to select the last record from every day using MySQL

Question

I know that there are already some solutions to this issue, but they don't seem to work for me.

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

The selection request 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 shows me the latest date from each day, but id and country do not match correctly. It actually gets the first id and country , as well as the last date .

What am I doing wrong?

Help with thanks!

Thanks,

Jack

Answer 1

Try this query to show the latest 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 WHERE clauses if you need to.

Answer 2

I think you should not group DATE(date) . I think you should group userid,country . Like this:

 SELECT MAX(date), userid, 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 userid,country ORDER BY date ASC 

Answer 3

You should get the correct results by changing ORDER BY to DESC instead of ASC