SQL - Selecting most recent rows by timestamp created that correlate to another timestamp column

Question

Lets say I have this table "forecasts"

| id | forecast_timestamp     | created_timestamp             |
| -- | ---------------------- | ----------------------------- |
| 1  | 2021-09-24 09:00:00+00 | 2021-09-23 20:01:03.891939+00 | <-- outdated forecast (it's been re-forecasted by the newer forecast, but I still want to keep it for historical purposes)
| 2  | 2021-09-24 09:00:00+00 | 2021-09-23 23:01:03.964427+00 | <-- newest forecast for 2021-09-24 09:00:00+00
| 3  | 2021-09-24 10:00:00+00 | 2021-09-23 22:43:22.142174+00 | <-- another forecast, but with completely different timestamps - however, it's still the most recent forecast for 2021-09-24 10:00:00+00

I want a query that will select the most recent forecasts based on the created_timestamp, and so it should return this:

| id | forecast_timestamp     | created_timestamp             |
| -- | ---------------------- | ----------------------------- |
| 2  | 2021-09-24 09:00:00+00 | 2021-09-23 23:01:03.964427+00 |
| 3  | 2021-09-24 10:00:00+00 | 2021-09-23 22:43:22.142174+00 |

How can I achieve this result?

Answer 1

Also see Postgres Select distinct on clause. (demo)

select distinct on (f.forecast_timestamp)
       f.* 
  from forcast f
 order by forecast_timestamp,created_timestamp desc;