How to select a row for every location for every day using SQL?-CodePudding

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 average temp of 3 latest forecasts 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 3

CodePudding user response：

Something like this should be fine. These return the entire forecast row for each day/location.

For version 8.0 :

WITH cte AS (
       SELECT *
            , ROW_NUMBER() OVER (PARTITION BY location, applicable_date ORDER BY created DESC ) AS rn
         FROM forecast
     )
SELECT *
  FROM cte
 WHERE rn = 1
;

For version 8.0 and before:

SELECT f.*
  FROM (
       SELECT location, applicable_date
            , MAX(created) AS max_created
         FROM forecast
        GROUP BY location, applicable_date
     ) AS cte
  JOIN forecast AS f
    ON f.location        = cte.location
   AND f.applicable_date = cte.applicable_date
   AND f.created         = cte.max_created
;

CodePudding user response：

SELECT location, 
       CAST(created AS DATE) `date`, 
-- or  applicable_date `date`,
       MAX(created) last_datetime
FROM forecast
GROUP BY 1, 2;