Select matching date to min() group by

Question

I'm trying to select the lowest temperatures and it's related measure time grouped by weather station. It works fine, except that the measureTime column not matching the selected temperature. Someone who can help me out?

    SELECT
        weatherstations.weatherstation_name AS name,
        min(weatherstations_data.weather_airtemp) AS airTemp,
        weatherstations_data.weather_measuretime AS measureTime
    FROM
        weatherstations 
    LEFT JOIN
        weatherstations_data 
    ON 
        weatherstations.weatherstation_id = weatherstations_data.weatherstation_id
    WHERE
        weatherstations_data.weather_airtemp IS NOT NULL 
    GROUP BY
        weatherstations.weatherstation_name
    ORDER BY 
        airTemp ASC 
    LIMIT 
        10

Current result looks like this:

name            airTemp   measureTime
---------------|---------|---------------------|
Latnivaara A   | 7.5     | 2019-07-27 00:00:00 |
Nikkaluokta A  | 8.6     | 2019-07-27 00:00:00 |
Graninge       | 8.9     | 2019-07-27 00:20:02 |
Rensjön A      | 8.9     | 2019-07-27 00:00:00 |
Pajala A       | 9.4     | 2019-07-27 00:00:00 |
Åkroken        | 9.4     | 2019-07-27 00:20:02 |
Norrhög        | 9.6     | 2019-07-27 00:20:02 |
Karesuando     | 9.8     | 2019-07-27 00:20:02 |
Noppikoski     | 9.8     | 2019-07-27 00:20:01 |
Nikkaluokta    | 9.8     | 2019-07-27 00:20:00 |

Desired result would be:

name            airTemp   measureTime
---------------|---------|---------------------|
Latnivaara A   | 7.5     | 2019-07-27 03:00:00 |
Nikkaluokta A  | 8.6     | 2019-07-27 03:00:00 |
Graninge       | 8.9     | 2019-07-27 04:20:01 |
Rensjön A      | 8.9     | 2019-07-27 04:00:00 |
Pajala A       | 9.4     | 2019-07-27 03:00:00 |
Åkroken        | 9.4     | 2019-07-27 05:20:02 |
Norrhög        | 9.6     | 2019-07-27 00:20:02 |
Karesuando     | 9.8     | 2019-07-27 03:00:00 |
Noppikoski     | 9.8     | 2019-07-27 01:20:00 |
Nikkaluokta    | 9.8     | 2019-07-27 02:00:00 |

A result set without a data set is like a stick without a lollipop. Not much fun. — Strawberry, Jul 27 '19 at 10:05
Possible duplicate of [Get records with max value for each group of grouped SQL results](https://stackoverflow.com/questions/12102200/get-records-with-max-value-for-each-group-of-grouped-sql-results) — Kaii, Jul 27 '19 at 10:08
This is a common problem in SQL known as "groupwise-maximum". Find a description of the problem and several solution approaches in the manual: https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html — Kaii, Jul 27 '19 at 10:10

score 0 · Answer 1 · answered Jul 27 '19 at 10:53

You should use a subquery for min temp and a join for retrieve the related measureTime

select  t.name, t.airTemp, d.measureTime 
from  (
    SELECT
        s.weatherstation_id 
        s.weatherstation_name AS name,
        min(d.weather_airtemp) AS airTemp
    FROM  weatherstations s
    LEFT JOIN weatherstations_data  d  ON  s.weatherstation_id = d.weatherstation_id
    WHERE d.weather_airtemp IS NOT NULL 
    GROUP BY s.weatherstation_name
    ORDER BY airTemp ASC 
    LIMIT  10
) t  
inner join  weatherstations_data d ON t.weatherstation_id = d.weatherstation_id
    AND  t.airTemp  = d.weather_airtemp

score 0 · Accepted Answer · edited Jan 11 '20 at 18:21

Do not use GROUP BY for a filtering query. A simple solution is a correlated subquery for filtering:

SELECT ws.weatherstation_name AS name,
       ws.weather_airtemp AS airTemp,
       wsd.weather_measuretime AS measureTime
FROM weatherstations ws LEFT JOIN
     weatherstations_data wsd
     ON ws.weatherstation_id = wsd.weatherstation_id
WHERE wsd.weather_airtemp IS NOT NULL AND
      wsd.weather_airtemp = (SELECT MIN(wsd2.weather_airtemp)
                             FROM weatherstations_data wsd2
                             WHERE wsd2.weatherstation_id = wsd.weatherstation_id
                            )
ORDER BY airTemp ASC 
LIMIT 10;

score 0 · Answer 3 · answered Jul 27 '19 at 11:15

You have to use aggregate functions before joining the tables.

    SELECT
    t1.weatherstation_name AS name,
    t2.weather_airtemp AS airTemp,
    t2.weather_measuretime AS measureTime
FROM
    weatherstations t1
LEFT JOIN(
   SELECT weatherstation_id , weather_measuretime , min(weather_airtemp) as weather_airtemp 
   FROM weatherstations_data 
   group by weather_airtemp
   ) t2 
ON 
    t1.weatherstation_id = t2.weatherstation_id
WHERE
    t2.weather_airtemp IS NOT NULL 
GROUP BY
    t1.weatherstation_name

LIMIT 
    10

Select matching date to min() group by

3 Answers3