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…