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 AS lat, stations.lon AS lon, stations.name AS name, climate.tmk AS tmk
FROM climate
INNER JOIN (
    SELECT climate.station, MAX(climate.date) AS DATE
    FROM climate
    GROUP BY climate.station
) AS c
ON climate.station = c.station AND climate.date = c.date
INNER JOIN stations
ON climate.station = stations.id
ORDER BY climate.station;