clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1579746 fiddles created (21345 in the last week).

CREATE TABLE tablename (RESERVATION int, PREFIX varchar2(2), FLIGHT int, ORIGIN varchar2(3), DESTINATION varchar2(3), DATE_FLIGHT timestamp) ;
 hidden batch(es)


INSERT ALL INTO tablename (RESERVATION, PREFIX, FLIGHT, ORIGIN, DESTINATION, DATE_FLIGHT) VALUES (111, 'LA', 123, 'LAX', 'MIA', '01-Feb-2020 12:00:00 AM') INTO tablename (RESERVATION, PREFIX, FLIGHT, ORIGIN, DESTINATION, DATE_FLIGHT) VALUES (111, 'LA', 122, 'MIA', 'SCL', '01-Feb-2020 10:30:00 AM') INTO tablename (RESERVATION, PREFIX, FLIGHT, ORIGIN, DESTINATION, DATE_FLIGHT) VALUES (111, 'LA', 667, 'MIA', 'SCL', '03-Feb-2020 02:15:00 PM') INTO tablename (RESERVATION, PREFIX, FLIGHT, ORIGIN, DESTINATION, DATE_FLIGHT) VALUES (111, 'LA', 882, 'SCL', 'ARG', '03-Feb-2020 04:00:00 PM') INTO tablename (RESERVATION, PREFIX, FLIGHT, ORIGIN, DESTINATION, DATE_FLIGHT) VALUES (111, 'LA', 111, 'SCL', 'ARG', '03-Feb-2020 11:00:00 PM') INTO tablename (RESERVATION, PREFIX, FLIGHT, ORIGIN, DESTINATION, DATE_FLIGHT) VALUES (111, 'LA', 966, 'SCL', 'ARG', '03-Feb-2020 11:30:00 PM') INTO tablename (RESERVATION, PREFIX, FLIGHT, ORIGIN, DESTINATION, DATE_FLIGHT) VALUES (111, 'LA', 622, 'SCL', 'ARG', '05-Feb-2020 08:00:00 AM') SELECT * FROM dual ;
7 rows affected
 hidden batch(es)


select * from tablename;
RESERVATION PREFIX FLIGHT ORIGIN DESTINATION DATE_FLIGHT
111 LA 123 LAX MIA 01-FEB-20 12.00.00.000000 AM
111 LA 122 MIA SCL 01-FEB-20 10.30.00.000000 AM
111 LA 667 MIA SCL 03-FEB-20 02.15.00.000000 PM
111 LA 882 SCL ARG 03-FEB-20 04.00.00.000000 PM
111 LA 111 SCL ARG 03-FEB-20 11.00.00.000000 PM
111 LA 966 SCL ARG 03-FEB-20 11.30.00.000000 PM
111 LA 622 SCL ARG 05-FEB-20 08.00.00.000000 AM
 hidden batch(es)


with min_max as ( select distinct reservation, min(date_flight) over(partition by reservation) as date_flight_min, max(date_flight) over(partition by reservation) as date_flight_max from tablename ) select m.reservation, t1.prefix as prefix_min, t1.flight as flight_min, t1.origin as origin_min, t1.destination as destination_min, m.date_flight_min, t2.prefix as prefix_max, t2.flight as flight_max, t2.origin as origin_max, t2.destination as destination_max, m.date_flight_max from min_max m join tablename t1 on t1.date_flight=m.date_flight_min join tablename t2 on t2.date_flight=m.date_flight_max
RESERVATION PREFIX_MIN FLIGHT_MIN ORIGIN_MIN DESTINATION_MIN DATE_FLIGHT_MIN PREFIX_MAX FLIGHT_MAX ORIGIN_MAX DESTINATION_MAX DATE_FLIGHT_MAX
111 LA 123 LAX MIA 01-FEB-20 12.00.00.000000 AM LA 622 SCL ARG 05-FEB-20 08.00.00.000000 AM
 hidden batch(es)