MySQL 8.0

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

create table test(
ID int NOT NULL ,
NAME VARCHAR (25)
);


insert into test values 
(1,'MICHAEL'),
(2,'JORDAN'),
(5,'DONALD'),
(7,'JAYCE'),
(8,'ROY'),
(11,'JOHN'),
(16,'DOE');
select ID,NAME, row_number() over(order by ID ASC ) as rownum
from test


ID
NAME
rownum


1
MICHAEL
1

2
JORDAN
2

5
DONALD
3

7
JAYCE
4

8
ROY
5

11
JOHN
6

16
DOE
7


create table test2(
ID int NOT NULL ,
NAME VARCHAR (25)
);


INSERT INTO test2( ID, NAME )
  WITH cte AS (
            select ID,NAME, row_number() over(order by ID ASC ) as rownum
            from test t1
  )
  SELECT rownum,NAME
  FROM cte;




select * from test2;


ID
NAME


1
MICHAEL

2
JORDAN

3
DONALD

4
JAYCE

5
ROY

6
JOHN

7
DOE