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;