When we have stats, often times the timestamp is involved. So, say we have a table like the following

CREATE TABLE IF NOT EXISTS `my_stats_table` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `created_datetime` datetime NOT NULL,
  `value1` int(11) NOT NULL,
  PRIMARY KEY (`id`)
)

Now, all I want is to grab data for each day. Of course, there could be many rows in a day, and there are many ways to handle that. In my case, I need to grab the last row since it’s most accurate. How to do that?

SELECT * 
FROM `my_stats_table`
WHERE created_datetime IN
  (SELECT max(created_datetime)
    FROM `my_stats_table`
    GROUP BY DATE(created_datetime)
  )

The magic here is GROUP BY DATE(created_datetime). Yea, we are not limited to grouping bare columns only.

What if I want to get the average of the rows of a particular day? Um…