locked
SQL 2000 : How to Increment a value in a select RRS feed

  • Question

  • HI,

    I need to increment a value for each row that my select-from output. How can I do this ?


    CREATE TABLE #Test ( TestString VARCHAR( 100 ) )

    INSERT INTO #Test VALUES ('Row1')
    INSERT INTO #Test VALUES ('Row2')
    INSERT INTO #Test VALUES ('Row3')


    I need a output like this :

    Column1     Column2
    1                Row1
    2                Row2
    3                Row3


    I don't want to add a new field on my #test table to be able to do this. I know it would work with an identity column in my #Test table, but it's not what I want. Lots of insert a done in this table ( in production, this table contains others fields ). The solution must be fast to execute.

    Thanks !





    Friday, August 29, 2008 11:24 AM

Answers

  • How to dynamically number rows in a SELECT Transact-SQL statement

    http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

     

    You need a criteria to sort the set. If you have duplicated rows by [c2], then you will need a tie braker.

     

    select

     (

    select count(*)

    from t as b

    where b.c2 <= a.c2

    ) as rn,

    a.c2

    from t as a

    order by rn

    GO

     

     

    AMB

    Friday, August 29, 2008 12:19 PM

All replies

  • These function does not exist in SQL Server 2000. I'm using the 2000 version of sql server like it is write in my subject...

    Do you have another idea ?
    Friday, August 29, 2008 11:38 AM
  • How to dynamically number rows in a SELECT Transact-SQL statement

    http://support.microsoft.com/default.aspx?scid=kb;en-us;186133

     

    You need a criteria to sort the set. If you have duplicated rows by [c2], then you will need a tie braker.

     

    select

     (

    select count(*)

    from t as b

    where b.c2 <= a.c2

    ) as rn,

    a.c2

    from t as a

    order by rn

    GO

     

     

    AMB

    Friday, August 29, 2008 12:19 PM
  •  

    CREATE TABLE #Test ( TestString VARCHAR( 100 ) )

    INSERT INTO #Test VALUES ('Row1')
    INSERT INTO #Test VALUES ('Row2')
    INSERT INTO #Test VALUES ('Row3')

    alter table #test

    add column column1 int identity(1,1)



    select * from #test

     


    Column1     TestString
    1                Row1
    2                Row2
    3                Row3

    -- This will be work for you.
    Friday, August 29, 2008 12:47 PM