I am trying to get the MAX and MIN values for the current month and the dates for these values.
I am getting the MAX and MIN values but I am not getting the dates for these values. Instead I get the first day of the month.
Here is my query:
SELECT DATE(CREATED) AS date
, MIN(ROUND(AMBIENT_TEMPERATURE,1)) AS min_temp
, MAX(ROUND(AMBIENT_TEMPERATURE,1)) AS max_temp
FROM WEATHER_MEASUREMENT
WHERE (MONTH(DATE(CREATED)) = Month(NOW()));
And this is what I get:
date min_temp max_temp
---------- -------- --------
2016-05-01 17.8 43.2
I would like to have a date for the max_temp and a date for the min_temp.
How can I do it?
Best Answer
Your query is invalid SQL, but MySQL accepts it with the default settings. It will give you a random result. You can change this behavior by adding 'ONLY_FULL_GROUP_BY' to sql_mode.
EDIT: Similar to @MickyT's solution but using a join instead:
It is not clear what time that should be used in case of a draw, the query outputs all times where a min or max temp occurred. Assuming that you would like the first occurrence, something like:
can be used.
I left out the predicate for the current month, but that is easily added. Since MySQL does not support
LATERAL
join, it is probably better to add that predicate to the derived table as well. I.e.As @RolandoMySQLDBA points out the WHERE clause can be rewritten in a index friendlier way.