MySQL GROUP BY DateTime with Aggregate Function

Question

I seem to remember this working as expected in Oracle, but I can't figure out how to get it right in MySQL.

Here is my query:

SELECT DateTimeStamp, MAX(Value) FROM t1
GROUP BY YEAR(DateTimeStamp), MONTH(DateTimeStamp), DAY(DateTimeStamp);

Running this produces results like:

DateTimeStamp       Value
2015-09-09 00:00:29 100
2015-09-10 00:00:05 58
2015-09-11 00:00:57 62
2015-09-12 00:00:49 69
2015-09-13 00:00:43 97

But I was expecting it to look like this, where the DateTimeStamps match up with the values:

DateTimeStamp       Value
2015-09-09 03:28:29 100
2015-09-10 03:29:05 58
2015-09-11 03:31:57 62
2015-09-12 03:30:49 69
2015-09-13 03:28:43 97

The correct maximum values are being selected, but the matching DateTimeStamps for those maximum values are not. Instead, it looks like the first DateTimeStamp value for each day is being selected. How can I change my query to display the matching DateTimeStamps?

Yes - that just selects the maximum DateTimeStamp for each day. — Ryan Foley, Sep 14 '15 at 20:14
_"The Row Holding the Maximum of a Certain Column"_ can be found from the [manual](https://dev.mysql.com/doc/refman/5.0/en/example-maximum-row.html) — vhu, Sep 14 '15 at 20:14
vhu, that works perfectly. In this scenario, I can account for ties by just using the max DateTimeStamp. Thanks! — Ryan Foley, Sep 14 '15 at 20:26

score 0 · Answer 1 · answered Sep 14 '15 at 20:18

If all you need is to strip the time section, you need to use the date function:

SELECT date(DateTimeStamp) AS DateTimeStamp, MAX(Value) AS MaxValue
FROM t1
GROUP BY date(DateTimeStamp);

Note: Grouping by something usually make sense when you select it. There is no point to group by YEAR(DateTimeStamp), MONTH(DateTimeStamp), DAY(DateTimeStamp) if all you need is the maximum value per each day.

MySQL GROUP BY DateTime with Aggregate Function

1 Answers1