Find source and final destination using sql-CodePudding

I have a table source_flight_destination like this

ID	Flight	source	destination
1	Indigo	II	BB
2	Air Asia	AA	II
3	Indigo	BB	JJ
4	spice jet	SS	BB
5	Indigo	JJ	SS
6	Air Asia	II	KK
7	spice jet	BB	JJ

The output should be flight, source and destination like this

flight	source	destination
Air Asia	AA	KK
Indigo	II	SS
Spicejet	SS	JJ

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?

CodePudding user response：

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.

CodePudding user response：

If I understood correctly what you are trying to do, taking the first row of a Flight for the Source and the last for the Destination, then this produces the list you said you wanted. I don't know how its performance will compare with forpas'. You'll just have to try it.

SELECT a.Flight, a.Source, c.Destination
FROM source_destination_flight a,
  (SELECT d.Flight, Min(d.id) AS Minid, Max(d.id) AS Maxid 
    FROM source_destination_flight d GROUP BY Flight) b,
  source_destination_flight c
WHERE a.id = b.Minid
AND c.id = b.Maxid;