How to generate an auto-increment field in a selected query

Question

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

 Ali Khani Elizabette Amini Britney Spears ,... 

I want to write a select query that generates a table as follows:

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

Thanks for your help.

Answer 1

If it's 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 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

 DECLARE @id INT SET @id = 0 UPDATE cartemp SET @id = CarmasterID = @id + 1 GO