[sql-server] How to generate auto increment field in select query

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.

This question is related to sql-server auto-increment

The answer is


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

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

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