Row_number() in SQL 2000 ...

  • To All Wizards and others,

    I need to generate a urn, in a on certain fields. Source fields on which to generate row_num like values looks like; e.g.

    col1,col2

    1,1

    1,10

    1,2

    2,6

    2,20

    And I want to generate a column (say col3) with values like below.

    col1,col2 ,col3

    1,1 , 1

    1,10 ,2

    1,2 ,3

    2,6 ,1

    2,20 ,2

    I hope that clears out my requirements. I have a quite big table so a efficient solution will be needed, as always 🙂

    I need to do this in SQL 2000 and now SQL 2005, which do have a row_number() function 🙁


    FP

    True Love is Like A Ghost. Everyone Talks About It & Nobody have seen it.

  • Are you basically looking to add an Identity Col?

    if so try this

    ALTER TABLE dbo.TestTable ADD

    Col3 int NOT NULL IDENTITY (1, 1)

    GO

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • It looks like you want a sequence within each set of values. Is this something permanent that you need or is it for reporting? Meaning do you want to add a column?

    An identity won't work if I am reading your sequence correctly. Instead you'd need to build a subquery to read the values out and order them within the groups. It will end up being a convoluted SQL query if this is for reporting and I hope this isn't run often.

  • Yeah you are right, i need excatly this.

    I kinda need a sequence within each set. I wish if I could see some sort of code for that ? Thanks for your reply.


    FP

    True Love is Like A Ghost. Everyone Talks About It & Nobody have seen it.

  • Sorry about my previous post I didn't read everything you first wrote 🙂

    this will work on SQL2005.

     

    SELECT

    Col1,Col2,Row_Number() OVER(Partition by col1 ORDER BY Col1)

    FROM TEst

    ----------------------------------------------
    Try to learn something about everything and everything about something. - Thomas Henry Huxley

    :w00t:
    Posting Best Practices[/url]
    Numbers / Tally Tables[/url]

    SQL-4-Life
  • Oops again!!

    Hey Chris,

    You looked over one little itsy bitsy detail ... i need to do this in SQL 2000.

    Thanks you.


    FP

    True Love is Like A Ghost. Everyone Talks About It & Nobody have seen it.

  • This can also be accomplished by using a numbers or tally table to ‘expand’ the groupings out.  For this to work, you’ll need an Identity column on your table (or you’ll have to read your data into a temp table or table variable with an Identity column).  You’ll also need to create either a static Numbers table or create one on the fly in your procedure/script.  See this example for creating your numbers table: http://www.sqlservercentral.com/columnists/mcoles/2547.asp.    

     

    DECLARE @Table TABLE (RowID int IDENTITY(1,1), col1 int,col2 int)

     

    INSERT INTO @Table (Col1, Col2)

    SELECT 1,1 UNION ALL

    SELECT 1,10 UNION ALL

    SELECT 1,2 UNION ALL

    SELECT 2,6 UNION ALL

    SELECT 2,20

     

    SELECT t1.Col1, t1.Col2, t2.SequenceNum

    FROM @Table t1

        INNER JOIN (

                SELECT N.Num as SequenceNum, t.Col1, (MinRow + N.Num) as RowID

                FROM dbo.Numbers N

                    INNER JOIN (

                            SELECT Col1, COUNT(*) as 'Rows', (MIN(RowID) - 1) as MinRow

                            FROM @Table

                            GROUP BY Col1

                            ) t

                    ON N.Num BETWEEN 0 AND [Rows]

                ) t2

        ON t1.RowID = t2.RowID

     

    John Rowan

    ======================================================
    ======================================================
    Forum Etiquette: How to post data/code on a forum to get the best help[/url] - by Jeff Moden

  • Using John's good test setup... this is 1 place where a triangular join may work well because the groups indicated by Col1 are so small...

    DECLARE @Table TABLE (RowID int IDENTITY(1,1), col1 int,col2 int)
     
    INSERT INTO @Table (Col1, Col2)
    SELECT 1,1 UNION ALL
    SELECT 1,10 UNION ALL
    SELECT 1,2 UNION ALL
    SELECT 2,6 UNION ALL
    SELECT 2,20
     SELECT Col1,
            Col2,
            Col3 = (SELECT COUNT(*) FROM @Table ti WHERE ti.Col1 = t.Col1 AND ti.RowID<= t.RowID)
       FROM @Table t  
      ORDER BY t.Col1,t.RowID

    If the groups are large, then I recommend a much different and faster approach then either John or my code using the proprietary UPDATE SET @variable=column=formula method.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • There is also nothing wrong with inserting into #Table with IDENTITY column (with desired order of records) and returning result from that table.

    In fact any of posted solutions creates temp table, it just happened "behind the scene", so performance will not suffer. Probably it will be even a little bit faster.

    _____________
    Code for TallyGenerator

  • Don't forget... Op wants numbers to restart at 1 at the beginning of each group.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • -- Prepare sample data

    DECLARE    @Sample TABLE (RowID INT IDENTITY(1,1), col1 int, col2 int)

     

    INSERT     @Sample

    SELECT     1, 1 UNION ALL

    SELECT     1, 10 UNION ALL

    SELECT     1, 2 UNION ALL

    SELECT     2, 6 UNION ALL

    SELECT     2, 20

     

    -- Show the data

    SELECT                s1.col1,

                          s1.col2,

                          s1.RowID - d.col2 + 1

    FROM                  @Sample AS s1

    INNER JOIN (

                                     SELECT                col1,

                                                          MIN(RowID) AS col2

                                     FROM                  @Sample

                                     GROUP BY   col1

                          ) AS d ON d.col1 = s1.col1

    ORDER BY   s1.RowID


    N 56°04'39.16"
    E 12°55'05.25"

  • Works fine (pretty cool solution, too, Peter) if you guarantee the order by loading a temp table... bit of a problem otherwise...

    -- Prepare sample data

    DECLARE    @Sample TABLE (RowID INT IDENTITY(1,1), col1 int, col2 int)

     

    INSERT     @Sample

    SELECT     1, 1 UNION ALL

    SELECT     2, 6 UNION ALL

    SELECT     1, 10 UNION ALL

    SELECT     1, 2 UNION ALL

    SELECT     2, 20

    -- Show the data

    SELECT                s1.col1,

                          s1.col2,

                          s1.RowID - d.col2 + 1

    FROM                  @Sample AS s1

    INNER JOIN (

                                     SELECT                col1,

                                                          MIN(RowID) AS col2

                                     FROM                  @Sample

                                     GROUP BY   col1

                          ) AS d ON d.col1 = s1.col1

    ORDER BY   s1.RowID

    Not bashing code folks... just want you to be aware that the code is very order sensitive.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Okay ... so this seems to be more intesting then what i originally thought it would be ... huh ?

    Great approch you all. But the problem is still unsolved.

    Also the 1st column is not rowid, it could be any value and is not in order !!! ...

    Thanks for everyone putting time and effort into this.


    FP

    True Love is Like A Ghost. Everyone Talks About It & Nobody have seen it.

  • Well, the sample data was ordered so I thought this was the case in production environment.

    Now OP finally seems to have released all information necessary.

     


    N 56°04'39.16"
    E 12°55'05.25"

  • Heh... and people ask me why I use IDENTITY columns...

    FP, think outside the box a bit... all the solutions that use an IDENTITY column work very fast.  If your table doesn't have an IDENTITY column to indicate the order, as you say, then even a Cursor wouldn't work for you.  You need to identify something in your table that DOES identify the order you want.

    Once you've done that, copy the data,  in the correct order, to a temp table that DOES have an IDENTITY column and use one of the solutions posted... it'll still be faster than a Cursor or While loop.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 15 posts - 1 through 15 (of 21 total)

You must be logged in to reply to this topic. Login to reply