How to generate auto increment field in select query

Question

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 1

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 

SQLFiddle

And for SQLServer

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

SQLFiddle

Answer 2

here's for SQL server, Oracle, PostgreSQL which support window functions.

SELECT  ROW_NUMBER() OVER (ORDER BY first_name, last_name)  Sequence_no,
        first_name,
        last_name
FROM    tableName

• SQLFiddle Demo

Answer 3

In the case you have no natural partition value and just want an ordered number regardless of the partition you can just do a row_number over a constant, in the following example i've just used 'X'. Hope this helps someone

select 
    ROW_NUMBER() OVER(PARTITION BY num ORDER BY col1) as aliascol1, 
    period_next_id, period_name_long
from 
(
  select distinct col1, period_name_long, 'X' as num
  from {TABLE} 
) as x