Adding a new column indicating the row number in mysql

Question

I have a table in MySQL like:

hiredate
2020-02-03
2019-12-03
2018-08-07

I want to add a new column right next to it and display the index number:

hiredate     no
2020-02-03   1
2019-12-03   2
2018-08-07   3

How can I do that?

Answer 1

In MySQL 8.0:

select hire_date, row_number() over(order by hire_date) no
from mytable
order by hire_date

In earlier versions, one method uses user variables:

set @no := 0;
select hire_date, @no := @no + 1 no
from mytable
order by hire_date;

Answer 2

In addition, you can create a row number column as an Auto Incremental column, which must be a primary key or use the following code:

ALTER TABLE table1

ADD COLUMN rowNumber INT NOT NULL;

SET @row_number = 0;

UPDATE table1

SET rowNumber= (@row_number:=@row_number + 1);