How to add counting rows to query?

Question

Suppose I have this query:

select id, name
from users

and it gives:

120 jack
130 jason
138 ban

How do I change the query so it will auto assign increasing row number and gives:

 1   120 jack
 2   130 jason
 3   138 ban

Answer 1

In order to give rows a number, you'd use ROW_NUMBER().

select row_number() over (order by id), id, name
from users
order by id;

Notice that you state the same order twice, once for the numbering, once for the output order.