Increment field in Select statement

Question

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

Answer 1

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

Answer 2

In SQL Server 2005 and above:

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

Answer 3

you can try using the

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

[Edit] Works with Sql Server 2005 and 2008