Search code examples
postgresqlsql-order-bydistinct-on

How to use multiple distinct on statements in Postgres while retaining the correct order

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.

Solution

  • I'd add dump_date DESC to the ORDER

    SELECT DISTINCT ON (time, forecast)
    *
FROM t
ORDER BY time, forecast, dump_date DESC