Post reply

another problem I can't seem to work out.

  • March 24, 2009 at 9:54 am

    #965349

    Ok, so do you want them matched in order of the columns? So for output you want (first few rows only)

    3191 3621004

    3193 3621005

    3340 3621006

    3391 3621007

    3392 3621008

    3394 3621009

    Correct?

    Can I use a couple temp tables? (needed because the Row_number function isn't available on SQL 2000)

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 24, 2009 at 9:57 am

    #965351

    GilaMonster (3/24/2009)

    Ok, so do you want them matched in order of the columns? So for output you want (first few rows only)

    3191 3621004

    3193 3621005

    3340 3621006

    3391 3621007

    3392 3621008

    3394 3621009

    Correct?

    Can I use a couple temp tables? (needed because the Row_number function isn't available on SQL 2000)

    I am on 2005 and tmp tables are fine by me.

    Also -- GSquared, you are correct, I don't understand the code you posted. 🙁

  • March 24, 2009 at 10:01 am

    #965355

    foxjazz,

    First, don't repost this thread, maybe we can get Steve to move it to a SQL Server 2005 forum.

    Second, since you are using SQL Server 2005, please post future questions in a SQL Server 2005 forum, not a SQL Server 7, 2000 forum. You will get answers that use SQL Server 2005 features if you do so.

  • March 24, 2009 at 10:06 am

    #965359

    I have an idea on how this could be done.

    Create 2 new tables with an identity, insert the records and join on identity.

    But I don't think that is a prefered way of getting it done. In fact I think it would use less resources to run a cursor.

  • March 24, 2009 at 10:10 am

    #965364

    foxjazz (3/24/2009)

    ...That is why the bit of humor, not taken that way it seems...

    Remember that humor can be a difficult thing among people with different native languages. One thing that can help is to include a "smiley face": 🙂 when making a joke. This helps the readers understand that you intended something to be funny.

    I forget to do this myself sometimes and I usually regret it when I do.

    [font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
    Proactive Performance Solutions, Inc.     [/font]    [font="Verdana"] "Performance is our middle name."[/font]

  • March 24, 2009 at 10:10 am

    #965365

    foxjazz (3/24/2009)

    I am on 2005 and tmp tables are fine by me.

    Please post 2005-related questions in the 2005 forums in the future. I'm not asking to be pedantic. I'm asking because the forum that questions are posted in is what we use to tell what features may or may not be used.

    Here's some of your sample data in a usable format. In future it would help greatly if you could provide this and the expected output in the initial post. It'll remove the need to go back and forward repeatedly to get info.

    insert into ProductControl ([ProductControlID]) Values (3621004)

    insert into ProductControl ([ProductControlID]) Values (3621005)

    insert into ProductControl ([ProductControlID]) Values (3621006)

    insert into ProductControl ([ProductControlID]) Values (3621007)

    insert into ProductControl ([ProductControlID]) Values (3621008)

    insert into ProductControl ([ProductControlID]) Values (3621009)

    insert into ProductControl ([ProductControlID]) Values (3621010)

    insert into ProductControl ([ProductControlID]) Values (3621011)

    insert into ProductControl ([ProductControlID]) Values (3621012)

    insert into ProductControl ([ProductControlID]) Values (3621013)

    Insert into NapaThirdPartyPN ([PN]) Values (3191)

    Insert into NapaThirdPartyPN ([PN]) Values (3193)

    Insert into NapaThirdPartyPN ([PN]) Values (3340)

    Insert into NapaThirdPartyPN ([PN]) Values (3391)

    Insert into NapaThirdPartyPN ([PN]) Values (3392)

    Insert into NapaThirdPartyPN ([PN]) Values (3394)

    Insert into NapaThirdPartyPN ([PN]) Values (3400)

    Insert into NapaThirdPartyPN ([PN]) Values (3420)

    Insert into NapaThirdPartyPN ([PN]) Values (3482)

    Insert into NapaThirdPartyPN ([PN]) Values (3518)

    Right, now for a fairly trivial update.

    DECLARE @max-2 INT

    SET @max-2 = 3621013

    Update NapaThirdPartyPN

    SET [pcid] = ProductControlID

    FROM

    (SELECT [ProductControlID], ROW_NUMBER() OVER (Order BY [ProductControlID])As RowNum FROM ProductControl

    WHERE [ProductControlID] > @max-2 - 25) sub1

    INNER JOIN

    (SELECT PN, ROW_NUMBER() OVER (Order BY PN)As RowNum FROM NapaThirdPartyPN) sub2

    On Sub1.RowNum = sub2.RowNum

    WHERE NapaThirdPartyPN.PN = sub2.PN

    The rownumber functions are used to assign an incrementing number to each row, so that they can be joined 'in order'

    That's why I was asking you for the order earlier, to know which column the row_number must be ordered by.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 24, 2009 at 10:10 am

    #965366

    Moving to 2005

  • March 24, 2009 at 10:17 am

    #965374

    foxjazz (3/24/2009)

    I have an idea on how this could be done.

    Create 2 new tables with an identity, insert the records and join on identity.

    But I don't think that is a prefered way of getting it done. In fact I think it would use less resources to run a cursor.

    On SQL 2000 that is how we would have had to do it. It's still lighter than a cursor. With two temp tables it'll be two inserts (of 25 rows each) and then 1 update of 25 rows.

    With a cursor it's, at minimum, 25 selects and 25 updates. Depending on the cursor type, there may also be a hidden insert into a temp table as well.

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • March 24, 2009 at 10:32 am

    #965384

    GilaMonster (3/24/2009)

    foxjazz (3/24/2009)

    I am on 2005 and tmp tables are fine by me.

    Please post 2005-related questions in the 2005 forums in the future. I'm not asking to be pedantic. I'm asking because the forum that questions are posted in is what we use to tell what features may or may not be used.

    Here's some of your sample data in a usable format. In future it would help greatly if you could provide this and the expected output in the initial post. It'll remove the need to go back and forward repeatedly to get info.

    insert into ProductControl ([ProductControlID]) Values (3621004)

    insert into ProductControl ([ProductControlID]) Values (3621005)

    insert into ProductControl ([ProductControlID]) Values (3621006)

    insert into ProductControl ([ProductControlID]) Values (3621007)

    insert into ProductControl ([ProductControlID]) Values (3621008)

    insert into ProductControl ([ProductControlID]) Values (3621009)

    insert into ProductControl ([ProductControlID]) Values (3621010)

    insert into ProductControl ([ProductControlID]) Values (3621011)

    insert into ProductControl ([ProductControlID]) Values (3621012)

    insert into ProductControl ([ProductControlID]) Values (3621013)

    Insert into NapaThirdPartyPN ([PN]) Values (3191)

    Insert into NapaThirdPartyPN ([PN]) Values (3193)

    Insert into NapaThirdPartyPN ([PN]) Values (3340)

    Insert into NapaThirdPartyPN ([PN]) Values (3391)

    Insert into NapaThirdPartyPN ([PN]) Values (3392)

    Insert into NapaThirdPartyPN ([PN]) Values (3394)

    Insert into NapaThirdPartyPN ([PN]) Values (3400)

    Insert into NapaThirdPartyPN ([PN]) Values (3420)

    Insert into NapaThirdPartyPN ([PN]) Values (3482)

    Insert into NapaThirdPartyPN ([PN]) Values (3518)

    Right, now for a fairly trivial update.

    DECLARE @max-2 INT

    SET @max-2 = 3621013

    Update NapaThirdPartyPN

    SET [pcid] = ProductControlID

    FROM

    (SELECT [ProductControlID], ROW_NUMBER() OVER (Order BY [ProductControlID])As RowNum FROM ProductControl

    WHERE [ProductControlID] > @max-2 - 25) sub1

    INNER JOIN

    (SELECT PN, ROW_NUMBER() OVER (Order BY PN)As RowNum FROM NapaThirdPartyPN) sub2

    On Sub1.RowNum = sub2.RowNum

    WHERE NapaThirdPartyPN.PN = sub2.PN

    The rownumber functions are used to assign an incrementing number to each row, so that they can be joined 'in order'

    That's why I was asking you for the order earlier, to know which column the row_number must be ordered by.

    I LIKE YOUR SOLUTION.

    It worked great, I wouldn't have thought to create an alias table in this situation.

    Although I do so an sooooo many other scenarios.

Viewing 9 posts - 16 through 23 (of 23 total)

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