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 |

Answer 1

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;