How to get the maximum and minimum values in each group as well as their times?

Question

Let's say I have a table MyTable with the columns Id, NumericValue and UTCTimestamp. I'm trying to group the results of my table MyTable by the hour of their timestamp and return the maximum NumericValuefor each group with its associated timestamp as well as the minimum NumericValue for each group with its associated timestamp value.

For now, I'm able to achieve the first part of my problem with the following query:

SELECT 
    HOUR(t.UTCTimestamp) AS `Hour`, 
    t.NumericValue AS MaximumValue,
    t.UTCTimestamp AS MaximumValueTime
FROM MyTable t
INNER JOIN (
    SELECT HOUR(t2.UTCTimestamp) AS `Hour`, MAX(t2.NumericValue) AS NumericValue
    FROM MyTable t2
    GROUP BY HOUR(t2.UTCTimestamp)
) maxNumericValue ON HOUR(t.UTCTimestamp) = maxNumericValue.`Hour` AND t.NumericValue = maxNumericValue.NumericValue
GROUP BY HOUR(t.UTCTimestamp);

Which was inspired by this answer.

Here's an MVCE.

How could I also show the minimum value for each group as well as the timestamp associated to it?

Answer 1

You can join to MyTable twice (and only use one aggregating subquery)

SELECT bounds.`Hour`
    , minT.NumericValue AS MinValue
    , minT.UTCTimestamp AS MinTime
    , maxT.NumericValue AS MaximumValue
    , maxT.UTCTimestamp AS MaximumValueTime
FROM (
    SELECT HOUR(t2.UTCTimestamp) AS `Hour`
         , MAX(t2.NumericValue) AS maxValue
         , MIN(t2.NumericValue) AS minValue
    FROM MyTable t2
    GROUP BY HOUR(t2.UTCTimestamp)
) bounds 
LEFT JOIN MyTable minT ON bounds.`Hour` = HOUR(minT.UTCTimestamp)
   AND bounds.minValue = minT.NumericValue
LEFT JOIN MyTable maxT ON bounds.`Hour` = HOUR(maxT.UTCTimestamp)
   AND bounds.maxValue = maxT.NumericValue
;