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

Try

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

Maybe this will work?

SELECT userid, username, date_createdFROM 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))

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.