MySQL: How to group data per hour and get the latest hour

Question

I'm trying to do a query that fetches data per hour but instead of the normal group by hour I want to narrow it down and only get the latest hour - meaning the newest data within that hour. With the picture shown below what I wanted to get is the rows with red boxes. If you will notice, first red row is 10:59:51 which means it's the only row that's within 10:00:00 and 10:59:59. For the rest of the rows that is on 12:00 and above I wanted to get 12:37:14 because it's the latest or newest for that hour range.

I have a simple query that groups the data by hour using HOUR() like:

SELECT userid, username, date_created
FROM user_accounts 
WHERE date_created >= '2009-10-27 00:00:00' AND date_created < '2009-10-27 23:59:59'
GROUP BY HOUR(date_created)

The query, however, is just grouping it by hour 10 and 12 which returns id 24 and 25 - but what I needed is id 24 and 28. Any ideas?

Answer 1

Try

SELECT  f.*
FROM    (
        SELECT  MAX(UNIX_TIMESTAMP(date_created)) AS mts
        FROM  user_accounts 
        GROUP BY HOUR(date_created)
        ) s
JOIN    user_accounts  f
ON      UNIX_TIMESTAMP(date_created) = s.mts
WHERE  DATE(date_created) = '2009-10-27'

Answer 2

Maybe this will work?

SELECT userid, username, date_created
FROM user_accounts 
WHERE userid IN (
  SELECT MAX(userid)
  FROM user_accounts 
  WHERE date_created >= '2009-10-27 00:00:00' AND date_created < '2009-10-27 23:59:59'
  GROUP BY HOUR(date_created)
)

Answer 3

I would have to assume you would also want it by day too if spanning multiple days, otherwise a max() by an hour could give you something from a week ago with one hour vs three days ago another, and current day with yet another... That, all if you spanned outside your WHERE clause specifically limiting to your single day range. Its not by specific user you want, but whoever had the last activity for that hour... could be the same person, could be completely different every time. I'm tacking on the specific date as part of my group test just in case you ever wanted to span a date range, but you can take it out too...

select STRAIGHT_JOIN
       ui.userid,
       ui.username,
       ui.date_created
   from 
       ( select
               date( date_created ),
               hour( date_created ),
               max( date_created ) as LastPerHour
            from
               user_accounts
            where
               date( date_created ) = '2009-10-27'
            group by
               date( date_created), 
               hour( date_created )) PreQuery
      join user_accounts ui
         on PreQuery.LastPerHour = ui.date_created

Again, I've included date as a grouping too if you wanted to span multiple days, just make sure your table has an index on date_created by itself... or at least in the first position of the index.