Picking the last record of each date in mysql

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` (
  `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?

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…

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s