MySQL: How to group data per hour and get the latest hour
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.