Post reply

Running count for distinct values

  • September 18, 2008 at 1:29 pm

    #382727

    I'm working on a migration script and drawing a blank on how to do the following. Basically, I have a Table that we'll call OrderDetails that has 1 to many OrderIDs in it. The new table will also have the OrderID column in it as well as a column with a sequential integer for each distinct OrderID. The example below might make more sense...

    CREATE TABLE OrderDetails (OrderID INT)

    INSERT INTO OrderDetails

    SELECT 1 UNION ALL

    SELECT 1 UNION ALL

    SELECT 2 UNION ALL

    SELECT 3 UNION ALL

    SELECT 4 UNION ALL

    SELECT 4 UNION ALL

    SELECT 4

    NewTable would look like this

    CREATE TABLE NewTable (OrderID INT, NewColumn INT)

    SELECT 1, 1 UNION ALL

    SELECT 1, 2 UNION ALL

    SELECT 2, 1 UNION ALL

    SELECT 3, 1 UNION ALL

    SELECT 4, 1 UNION ALL

    SELECT 4, 2 UNION ALL

    SELECT 4, 3

    This is a table with over a million records so I'm trying not to loop through every record if I can avoid it.

    Thank you in advance!

    Greg

  • September 18, 2008 at 1:42 pm

    #873896

    Depending on what you need this for, you might care to look at the ROW_NUMBER() function in 2005, or use the techniques described in this article.

    http://www.sqlservercentral.com/articles/Advanced+Querying/61716/[/url]

    ----------------------------------------------------------------------------------
    Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?

  • September 18, 2008 at 2:01 pm

    #873910

    That's it. Thanks, Matt.

    Greg

Viewing 3 posts - 1 through 2 (of 2 total)

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