CODEWITHSUNDEEP
mysqlmax
Jack Lenox
Jack Lenox

Reputation: 357

How to select last record from each day with MySQL

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

Upvotes: 5

Views: 12337

Answers (3)

Dan Stotmeister
Dan Stotmeister

Reputation: 1

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

Upvotes: -1

Devart
Devart

Reputation: 121902

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.

Upvotes: 11

Arion
Arion

Reputation: 31239

I think you should not group by DATE(date). I think you should group by 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

Upvotes: 0

Related Questions