CODEWITHSUNDEEP
sql-serverauto-increment
wf Khani
wf Khani

Reputation: 1165

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.

Upvotes: 115

Views: 381889

Answers (4)

balgar jagpal
balgar jagpal

Reputation: 81

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

Upvotes: 7

Dev3
Dev3

Reputation: 25

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

Upvotes: -1

John Woo
John Woo

Reputation: 263693

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

Upvotes: 43

peterm
peterm

Reputation: 92785

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

Upvotes: 189

Related Questions