Find source and final destination using sql

I have a table source_flight_destination like this

The output should be flight, source and destination like this

I came up with a working solution:

with ranked as (
    select *,
    row_number() over (partition by flight order by id asc) as rn
    from source_destination_flight
),
minima as (
    select flight, min(rn) as minrn from ranked group by flight ),
maxima as (
    select flight, max(rn) as maxrn from ranked group by flight),
sourced as (
        select 
              r.flight, 
              r.source as source 
        from ranked r 
        join minima m1 on m1.flight=r.flight and m1.minrn=r.rn
),
destination as (
     select 
           r1.flight, 
           r1.destination as destination 
     from ranked r1 
     join maxima m2 
     on m2.flight=r1.flight and m2.maxrn=r1.rn
)
select
    s.flight, s.source, d.destination from sourced s join destination d on s.flight=d.flight

The idea was to:

• give a row_number() grouped by flight as partition,
• find minima and maxima of row_number for each partition,
• select source and destination by filtering out on basis of minima and maxima.

Yet this solution looks downright ugly and I am sure there is a much simpler solution out there.

Can anyone give me pointers?

For this sample data you can use window function FIRST_VALUE():

SELECT DISTINCT Flight,
       FIRST_VALUE(source) OVER (PARTITION BY Flight ORDER BY ID) AS source,
       FIRST_VALUE(destination) OVER (PARTITION BY Flight ORDER BY ID DESC) AS destination
FROM source_destination_flight;

See the demo.