Sequential numbering of rows from select?

• xnl28

  Ten Centuries

  Points: 1094

  More actions

  July 13, 2004 at 7:58 am

  #85737

  Hi,

  I have a problem that is probably very simple, but my brain isn't working too well today!

  I want to have the row number of each row from a select statement included in the output. For example,

  TableA:Col1, Col2

            5, Product1

            8, Product2

  Select * from TableA give me

            5, Product1

            8, Product2

  What I want is to perform a select that will give me this:

            1, 5, Product1

            2, 8, Product2

  I have tried selecting into a temporary table and then using ALTER TABLE #TMP ADD line INT IDENTITY(1,1), but any subsequent select in my stored procedure doesn't seem to recognise the newly added column.

  Any ideas?

• Allen Cui-55137

  SSC Guru

  Points: 51650

  More actions

  July 13, 2004 at 9:14 am

  #514412

  select identity(int,1,1) as row_number, * into #temptable from yourtable

  select * from #temptable

  drop table #temptable

   

• xnl28

  Ten Centuries

  Points: 1094

  More actions

  July 13, 2004 at 9:48 am

  #514420

  That was it!  Many thanks