Vfrdtyky

I am trying to find previous flight from the historical flight table, i have only 1 historical table wit tail number of plane and scheduled date departure in it, i want to make a new table with information of previous flight from that plane, below is example of the table

Original Table :

|flight number|scheduled departure flight|

| A           | 1 Jan 2018 10:00         |

| A           | 1 Jan 2018 19:00         |

| B           | 1 Jan 2018 11:00         |

| B           | 1 Jan 2018 19:00         |

| B           | 1 Jan 2018 21:00         |

Result expected :

|flight number|scheduled departure flight| previous scheduled departure |

| A           | 1 Jan 2018 10:00         | NULL                         |

| A           | 1 Jan 2018 19:00         | 1 Jan 10:00                  |

| B           | 1 Jan 2018 11:00         | NULL                         |

| B           | 1 Jan 2018 19:00         | 1 Jan 2018 11:00             |

| B           | 1 Jan 2018 21:00         | 1 jan 2018 19:00             |

i have been working on it for a day, appreciate any help from you

You are looking for LAG window function, but which support mysql version higher than 8.0

You can try to use a subquery to make a lag window function.

Schema (MySQL v5.7)

Query #1

SELECT `flight number`,

       `scheduled departure flight`,

       (select `scheduled departure flight` 

       from T tt

       where t1.`scheduled departure flight` > tt.`scheduled departure flight`

       AND t1.`flight number` = tt.`flight number`

       order by tt.`scheduled departure flight` desc

       limit 1

       ) 'previous scheduled departure'

FROM T t1;



| flight number | scheduled departure flight | previous scheduled departure |

| ------------- | -------------------------- | ---------------------------- |

| A             | 2018-06-01 10:00:00        |                              |

| A             | 2018-06-01 19:00:00        | 2018-06-01 10:00:00          |

| B             | 2018-06-01 11:00:00        |                              |

| B             | 2018-06-01 19:00:00        | 2018-06-01 11:00:00          |

| B             | 2018-06-01 21:00:00        | 2018-06-01 19:00:00          |

View on DB Fiddle

If your mysql version support LAG you can try this.

Schema (MySQL v8.0)

CREATE TABLE T(

   `flight number` varchar(5),

    `scheduled departure flight` datetime

);







INSERT INTO T VALUES ('A','2018-06-01 10:00');

INSERT INTO T VALUES ('A','2018-06-01 19:00');

INSERT INTO T VALUES ('B','2018-06-01 11:00');

INSERT INTO T VALUES ('B','2018-06-01 19:00');

INSERT INTO T VALUES ('B','2018-06-01 21:00');

Query #1

SELECT `flight number`,

       `scheduled departure flight`,

       LAG(`scheduled departure flight`) OVER(PARTITION BY  `flight number` ORDER BY `scheduled departure flight`) 'previous scheduled departure'

FROM T t1;

View on DB Fiddle