1

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?

  • 3
    Assuming that I've read the question right, you want the day in the month which was coldest and the day which was hottest? How do you want to handle days with the same temperature that happen to be the lowest or highest? First day, last day, all? – MickyT May 25 '16 at 3:08
  • A good question I haven't thought about, maybe the latest ... What do you think ? – JEROME C May 25 '16 at 13:40
3

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:

SELECT M.*
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);

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:

SELECT MIN(M.CREATED), M.AMBIENT_TEMPERATURE
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);
GROUP BY M.AMBIENT_TEMPERATURE

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.

SELECT MIN(M.CREATED), M.AMBIENT_TEMPERATURE
FROM WEATHER_MEASUREMENT M
JOIN (
   SELECT MONTH(CREATED) AS MONTH
        , MIN(AMBIENT_TEMPERATURE) AS MIN_TEMP
        , MAX(AMBIENT_TEMPERATURE) AS MAX_TEMP
   FROM WEATHER_MEASUREMENT M
   WHERE (MONTH(DATE(CREATED)) = Month(NOW()))
   GROUP BY MONTH(CREATED)
) X
    ON MONTH(M.CREATED) = X.MONTH
   AND M.AMBIENT_TEMPERATURE IN (X.MIN_TEMP, X.MAX_TEMP);
WHERE (MONTH(DATE(CREATED)) = Month(NOW()))
GROUP BY M.AMBIENT_TEMPERATURE

As @RolandoMySQLDBA points out the WHERE clause can be rewritten in a index friendlier way.

  • Hello thank you for your answer. Your query returns me multiple answers date | min_temp | max_temp | +------------+----------+----------+ | 2016-05-01 | 23.0 | 23.1 | | 2016-05-16 | 17.8 | 24.2 | | 2016-05-17 | 17.9 | 19.0 | | 2016-05-21 | 18.7 | 21.1 | | 2016-05-22 | 17.9 | 25.6 | | 2016-05-24 | 19.2 | 43.1 – JEROME C May 24 '16 at 21:19
  • Can you provide create table and insert statement (or an sqlfiddle) and expected output – Lennart May 25 '16 at 8:46
  • In fact, it returns me min temp and max temp for every days of the month, I would like only the min temp and the max temp of the current month. Thank you anyway – JEROME C May 25 '16 at 12:31
1

This is my interpretation of your requirement.
As a simple query, this will return what you want, however you will probably want to try other options to make it perform. I don't use MySQL, but in SQL Server I would look at trying CROSS APPLIES or some sort of grouping option.
This query will return all the dates on which the minimum or maximum occurred. You can of course then filter that to suit what you require.

SELECT *
FROM WEATHER_MEASUREMENT M
WHERE AMBIENT_TEMPERATURE IN
  (SELECT MIN(AMBIENT_TEMPERATURE) TEMP
   FROM WEATHER_MEASUREMENT L
   WHERE MONTH(L.CREATED) = MONTH(M.CREATED)
   UNION ALL
   SELECT MAX(AMBIENT_TEMPERATURE) TEMP
   FROM WEATHER_MEASUREMENT L
   WHERE MONTH(L.CREATED) = MONTH(M.CREATED)
  )

SQL Fiddle

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.