Select from table max temperature on specific date

Question

I have a table with the following data

Temperature    DateTimeValue         WarnCrit
29.1        2020-06-22 10:08:30         0
29.2        2020-06-22 09:38:28         0
29.2        2020-06-22 09:08:26         0
28.9        2020-06-22 08:38:26         0
28.7        2020-06-22 08:08:24         0
28.7        2020-06-22 07:38:22         0
29.2        2020-06-22 07:08:21         0
29.8        2020-06-22 06:38:20         0
29.9        2020-06-22 06:08:18         0

i like to make a select to find the maximum,minimum,average temperature on specific date so i use the following:

SELECT max(Temperature) as maxtemp
     , min(Temperature) as mintemp
     , avg(Temperature) as avtemp 
  FROM TempHistory 
 WHERE date(DateTimeValue)='2020-06-22'

This work correct, but i like also to have the specific time that this temperature occured. so i change it to :

SELECT * 
  from TempHistory 
 where DateTimeValue = '2020-06-22' 
   and Temperature = (select max(Temperature) from TempHistory)

and this return nothing.

Answer 1

You can use window functions, particularly first_value():

SELECT DISTINCT max(Temperature) OVER () as maxtemp,
       min(Temperature) OVER () as mintemp,
       avg(Temperature) OVER () as avtemp,
       FIRST_VALUE(DateTimeValue) OVER (ORDER BY Temperature ASC) as dt_at_min,
       FIRST_VALUE(DateTimeValue) OVER (ORDER BY Temperature DESC) as dt_at_max
FROM TempHistory 
WHERE DateTimeValue >= '2020-06-22' AND
      DateTimeValue < '2020-06-23';

Unfortunately, MySQL (and SQL in general) doesn't have a "first" or "last" aggregation function. However, this is quite similar.

Also note the change to the WHERE. This allows the query to make use of an index on DateTimeValue -- if one is available.