I have a table, which looks like this:

I need to extract only one row with the closest 'next_flight_schedule' that happened after 'check_schedule'. Like this:

One column for each aircraft, but with the smallest date which happened after check_schedule. All columns should be displayed in the query.

CodePudding user response：

One option might be this (read comments within code):

SQL> with test (aircraft, flight, check_schedule, next_flight_schedule) as
  2  -- sample data
  3  (select 'Boeing', 123, to_date('10.02.2022 21:00', 'dd.mm.yyyy hh24:mi'), to_date('11.02.2022 14:30', 'dd.mm.yyyy hh24:mi') from dual union all
  4   select 'Boeing', 234, to_date('10.02.2022 19:00', 'dd.mm.yyyy hh24:mi'), to_date('11.02.2022 22:50', 'dd.mm.yyyy hh24:mi') from dual union all
  5   select 'Boeing', 345, to_date('10.02.2022 21:00', 'dd.mm.yyyy hh24:mi'), to_date('11.02.2022 04:30', 'dd.mm.yyyy hh24:mi') from dual union all
  6   select 'Airbus', 789, to_date('21.02.2022 16:30', 'dd.mm.yyyy hh24:mi'), to_date('22.02.2022 01:20', 'dd.mm.yyyy hh24:mi') from dual union all
  7   select 'Airbus', 890, to_date('21.02.2022 22:30', 'dd.mm.yyyy hh24:mi'), to_date('22.02.2022 02:20', 'dd.mm.yyyy hh24:mi') from dual
  8  ),
  9  --

 10  temp as
 11  -- MAX CHECK_SCHEDULE per aircraft
 12  (select aircraft, max(check_schedule) max_check_schedule
 13   from test
 14   group by aircraft
 15  )
 16  select t.*
 17  from test t join temp a on a.aircraft = t.aircraft
 18  where t.next_flight_schedule =
 19                                 -- the 1st NEXT_FLIGHT_SCHEDULE that follows the MAX CHECK_SCHEDULE
 20                                (select min(t1.next_flight_schedule)
 21                                  from test t1
 22                                  where t1.aircraft = t.aircraft
 23                                    and t1.next_flight_schedule > a.max_check_schedule
 24                                 );

AIRCRA     FLIGHT CHECK_SCHEDULE   NEXT_FLIGHT_SCHE
------ ---------- ---------------- ----------------
Boeing        345 10.02.2022 21:00 11.02.2022 04:30
Airbus        789 21.02.2022 16:30 22.02.2022 01:20

SQL>

CodePudding user response：

I need smallest value from next_flight_schedule for each aircraft

You can use the ROW_NUMBER analytic function:

SELECT aircraft,
       flight,
       check_schedule,
       next_flight_schedule
FROM   (
  SELECT t.*,
         ROW_NUMBER() OVER (
           PARTITION BY aircraft
           ORDER BY next_flight_schedule
         ) AS rn
  FROM   table_name t
)
WHERE  rn = 1

Which, for the sample data:

CREATE TABLE table_name (
  aircraft             VARCHAR2(10),
  flight               NUMBER,
  check_schedule       DATE,
  next_flight_schedule DATE
);

INSERT INTO table_name (aircraft, flight, check_schedule, next_flight_schedule)
  SELECT 'Boeing', 123, TIMESTAMP '2022-02-10 21:00:00', TIMESTAMP '2022-02-11 14:30:00' FROM DUAL UNION ALL
  SELECT 'Boeing', 234, TIMESTAMP '2022-02-10 19:00:00', TIMESTAMP '2022-02-11 22:50:00' FROM DUAL UNION ALL
  SELECT 'Boeing', 345, TIMESTAMP '2022-02-10 21:00:00', TIMESTAMP '2022-02-11 04:30:00' FROM DUAL UNION ALL
  SELECT 'Airbus', 789, TIMESTAMP '2022-02-21 16:30:00', TIMESTAMP '2022-02-22 01:20:00' FROM DUAL UNION ALL
  SELECT 'Airbus', 890, TIMESTAMP '2022-02-21 22:30:00', TIMESTAMP '2022-02-22 02:20:00' FROM DUAL;

Outputs:

AIRCRAFT	FLIGHT	CHECK_SCHEDULE	NEXT_FLIGHT_SCHEDULE
Airbus	789	2022-02-21 16:30:00	2022-02-22 01:20:00
Boeing	345	2022-02-10 21:00:00	2022-02-11 04:30:00

If you want to filter rows to get the earliest next_flight_schedule that is also after the maximum check_schedule then:

SELECT aircraft,
       flight,
       check_schedule,
       next_flight_schedule
FROM   (
  SELECT m.*,
         ROW_NUMBER() OVER (
           PARTITION BY aircraft
           ORDER BY next_flight_schedule
         ) AS rn
  FROM   (
    SELECT t.*,
           MAX(check_schedule) OVER (PARTITION BY aircraft) AS max_check
    FROM   table_name t
  ) m
  WHERE  next_flight_schedule >= max_check
)
WHERE  rn = 1

db<>fiddle here