sql - Adding a Row Number in Query

Question

I have a table in my database in Access 2013.

Table : city

ID_city city
1       Tetuan
5       Rabat
9       Marrakech
10      Agadir
15      Laayoun

I wish to add the Rowid number beside them:

Rowid   ID_city city
1       1       Tetuan
2       5       Rabat
3       9       Marrakech
4       10      Agadir
5       15      Laayoun

Answer 1

One way to do this is to use the count function in a subquery. Not sure it scales well though and there are probably better ways...

select 
    (select count(*) from city where ID_city <= t1.ID_city) as row_number,
    *
from city t1