Select most recent value each given day, based on the latest updated timestamp-CodePudding

For my data depicted below, I need to edit my SQL so that for each given day ('gas_date') I need to select only a single row for each day where this row has the most recent 'LastUpdated' value which I have highlighted in yellow below.

I tried the following sql, however this only selects the max value for LastUpdated from the entire dataset over the entire gas_date range, rather than the max LastUpdated value from each gas day.

SELECT gas_date, pipeline_point_name, value, LastUpdated from table12
WHERE LastUpdated = (SELECT MAX(LastUpdated) FROM table12)

CodePudding user response：

this should work, the 'Group By' function can be used outside or inside a sub query in this case

SELECT gas_date, pipeline_point_name, value, LastUpdated from table12 GROUP BY gas_date

CodePudding user response：

SELECT gas_date, pipeline_point_name, value, LastUpdated from table12 WHERE LastUpdated in ( SELECT MAX(LastUpdated) FROM table12 group by gas_date )

Subquery returns the Latest Lastupdated date for each day