How to generate auto increment field in select query

sql-server auto-increment

wf Khani · May 15, 2013 · Viewed 259.4k times · Source

For example I have a table with 2 columns, first_name and last_name with these values

Ali           Khani
Elizabette    Amini
Britney       Spears
,...

I want to write a select query that generate a table like this:

1     Ali           Khani
2     Elizabette    Amini
3     Britney       Spears
,...

Thanks for your help.

Answer

If it is MySql you can try

SELECT @n := @n + 1 n,
       first_name, 
       last_name
  FROM table1, (SELECT @n := 0) m
 ORDER BY first_name, last_name

And for SQLServer

SELECT row_number() OVER (ORDER BY first_name, last_name) n,
       first_name, 
       last_name 
  FROM table1