I have a database where some forecasts for two locations are stored. For every location there are forecasts for 3 days and for every day there are multiple forecasts every hour. I want to select the latest forecast for each location for every day. The location is saved as "location", each day is saved as "applicable_date" and for every day there is a forecast each hour saved as "created". I am trying something like this but does not work:
SELECT * FROM (
SELECT * FROM `forecast` GROUP BY location
) GROUP BY applicable_date
ORDER BY created DESC
LIMIT 1
>Solution :
SELECT location,
CAST(created AS DATE) `date`,
-- or applicable_date `date`,
MAX(created) last_datetime
FROM forecast
GROUP BY 1, 2;