CODEWITHSUNDEEP
sql-server
R0b0tn1k
R0b0tn1k

Reputation: 4306

Increment field in Select statement

I have some data which i dont have a group statement in, and would not like to have a group statement in. But i would like to have an increment field so i can can do a reporting services zebra table.

So, how do i turn this data:

region    country     office    somedata     someotherdata
EUROPE    Austria     Vienna    12           2
ASIA      India       Delhi     22           4

Into

region    country     office    somedata     someotherdata     IncField
EUROPE    Austria     Vienna    12           2                 1
ASIA      India       Delhi     22           4                 2

Upvotes: 3

Views: 3641

Answers (3)

Binoj Antony
Binoj Antony

Reputation: 16204

you can try using the

SELECT ROW_NUMBER() OVER (ORDER BY SomeData) AS IncField
, *
FROM TableName

[Edit] Works with Sql Server 2005 and 2008

Upvotes: 5

Quassnoi
Quassnoi

Reputation: 425803

In SQL Server 2005 and above:

SELECT  *, ROW_NUMBER() OVER (ORDER BY someotherdata) AS IncField
FROM    mytable

Upvotes: 1

kevchadders
kevchadders

Reputation: 8335

Insert your data into a temp table, which has an additional field (IDENTITY) as an incremental counter.

Upvotes: 0

Related Questions