MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE flight
    (id int,`flight_number` int, `date` datetime)
;
    
INSERT INTO flight
    (id,`flight_number`, `date`)
VALUES
    (1,123, '2021-09-16 00:00:00'),
    (2,123, '2021-09-16 00:00:00'),
    (3,123, '2021-09-17 00:00:00'),
    (4,124, '2021-09-18 00:00:00'),
    (5, 124, '2021-09-18 00:00:00')
;
delete ff from 
flight ff
inner join (


select id,flight_number, row_number() over (partition by flight_number order by date) as RN
from flight f

) dups
on ff.ID = dups.ID
where dups.rn > 1
select * from flight


id
flight_number
date


1
123
2021-09-16 00:00:00

4
124
2021-09-18 00:00:00