Increment field in Select statement

Question

I have some data in which I did not contain a group operator, and would not want to have a group operator. But I would like to have an increment field, so I can make a report services zebra table.

So, how do I include this data:

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

IN

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

Answer 1

you can try using

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

[Change] Works with Sql Server 2005 and 2008

Answer 2

In SQL Server 2005and above:

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

Answer 3

Paste your data into a temporary table that has an additional field (IDENTITY) as an incremental counter.