Odtnhj

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