Return counter in SELECT statement

• Wookie DBA

  Say Hey Kid

  Points: 705

  More actions

  August 3, 2004 at 6:26 am

  #108767

  I'm returning some records and would like to have the first field be a counter. 1 for the first record, 2 for the second, 3 for the third, etc. 

  Is there a way to do this in a select?

  thanks

• David Burrows

  SSC Guru

  Points: 65142

  More actions

  August 3, 2004 at 7:05 am

  #517426

  If the data is sorted on a column with unique values then you can do the following (uses pubs for example)

  select (select count(*)

    from pubs.dbo.stores s2

    where s2.stor_id <= s.stor_id) [Counter],*

  from pubs.dbo.stores s

  order by s.stor_id

  Otherwise, you can create a temp table with an identity column, insert the data into the temp table, then select from the temp table.

  Far away is close at hand in the images of elsewhere.
  Anon.

• joemai

  Hall of Fame

  Points: 3496

  More actions

  August 6, 2004 at 2:35 pm

  #518082

  Here is what Allen Cui showed me before:

  select a.*, IDENTITY(int, 1,1)  as Counter

  into #tmptb  

  from  yourtable a