How to optimize a query based on another aggregation query-CodePudding

There is a table:

create table transfers(
ts timestamp without time zone,
departure varchar(3),
destination varchar(3),
price numeric
);

containing:

ts                      departure  destination  price
______________________________________________________
2020-11-01T15:00:00Z    Chicago     Berlin      500
2020-11-01T12:00:05Z    Chicago     Berlin      400
2020-11-01T20:01:00Z    Chicago     Berlin      920
2020-11-01T20:01:00Z    London      Berlin      800
2020-11-01T22:00:00Z    London      Berlin      750

I need to get prices for each departure point with the highest timestamp so the result should be:

ts                      departure  destination  price
______________________________________________________
2020-11-01T20:01:00Z    Chicago     Berlin      920
2020-11-01T22:00:00Z    London      Berlin      750

I did it using query:

with max_ts_per_departure AS (
        SELECT MAX(ts) AS ts, departure
        FROM transfers
        GROUP BY departure
)
        SELECT t.ts, mtpd.departure, t.price
        FROM max_ts_per_departure AS mtpd
        INNER JOIN transfers AS t
        ON mtpd.departure=t.departure AND mtpd.ts=t.ts;

Is there a way to shorten the query and avoid using WITH statement without slowing the execution time too much? Real data is much larger.

CodePudding user response：

Try using DISTINCT ON:

SELECT DISTINCT ON (departure) *
FROM transfers
ORDER BY departure, ts DESC;