Row ID

[IanWaterman]

I want to add an auto incrementing Row Id to my query. Such that if query returns 10 records. I want each numbered from 1 to 10.

Is this possible within a standard select statement?

Thank you

Ian

 

[markros]

Sure,

SQL Server 2005+

select myField, row_number() over (order by SomeField) as Row from myTable

SQL Server 2000

select *, int(identity) as Row into #TempTable from myTable order by SomeField

select * from #TempTable

drop table #TempTable

PluralSight Learning Library

 

[IanWaterman]

Thanks for this.

I looked on my TRANSACT SQL reference and there is no mention of row_number().

Ian

 

[markros]

What is your SQL Server version?

Try

select @@Version

See ROW_NUMBER() in BOL

http://msdn.microsoft.com/en-us/library/ms186734.aspx

PluralSight Learning Library

 

[IanWaterman]

That explains it. Obviously not updated by favourites link recently. Mine was pointing to

http://msdn.microsoft.com/en-us/library/aa299742(SQL.80).aspx

Now going to latest as per your post.

Thanks again

Ian