Wir hätten gerne aus einer Tabelle nur die Zeile wo climate.date max ist, gruppiert nach climate.stations und wollen noch stations.name anzeigen.

SELECT stations.lat, stations.lon, stations.name, climate.tmk
FROM climate
INNER JOIN (
    SELECT climate.station, MAX(climate.date) AS DATE
    FROM climate
    GROUP BY stations
) AS c
ON climate.station = c.station AND climate.date = c.date
INNER JOIN stations
ON climate.station = stations.id
ORDER BY climate.station;