MySQL 8.0

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

select version();


version()


8.0.36


create table mytable (id int, date date);
insert into mytable values (2, '2020-01-01');
insert into mytable values (1, '2021-01-01');
insert into mytable values (8, '2022-01-01');
create view myview as
select t.*, row_number() over(order by date, id) new_id from mytable t
select * from myview;


id
date
new_id


2
2020-01-01
1

1
2021-01-01
2

8
2022-01-01
3