0

I have a table with weather forecasts in Postgres that looks like this

enter image description here

Here, a wind and a solar forecast is published every 15 minutes for the same time. I wish to select the latest wind and solar forecast from this table using a distinct on() statement. However, when I use this only on the time column, it deletes the wind forecast since that forecast is dumped one minute before the solar forecast. I have tried using distinct on(time, forecast) but then the order somehow is messed up and I no longer take the latest dump_date (see below)

enter image description here

How can I use a distinct on() statement on multiple columns while still retaining the order? The query I'm using now is

select
    distinct on ("time", "forecast") *
from table
order by "time"

It is important that this query stays dynamic, so hardcoding the dump_date is not an option for me.

Wouter
  • 477
  • 2
  • 6
  • 8
  • Please don't add images. It makes it extremly complecated to copy your test data. Please add some sample data and expected output as copyable text. – S-Man Nov 27 '20 at 09:52

1 Answers1

1

I'd add dump_date DESC to the ORDER

SELECT DISTINCT ON (time, forecast)
    *
FROM t
ORDER BY time, forecast, dump_date DESC
S-Man
  • 22,521
  • 7
  • 40
  • 63