Post reply

Any better way to do this?

  • July 23, 2008 at 11:39 pm

    #126700

    Hi All,

    I have the following situation:

    Create table #a

    (aa varchar(2), bb int)

    insert into #a values('',1)

    insert into #a values('',1)

    insert into #a values('',1)

    insert into #a values('',3)

    insert into #a values('',2)

    insert into #a values('',2)

    insert into #a values('',2)

    insert into #a values('',3)

    Now, what I want to do is update the top 1 aa value for each unique bb value.

    I know I can probably put the distinct bb in another table, and then use a while loop (not a cursor) to do this.

    But is there 1 single Update statement that can help me to achieve this?

    Your help is appreciated.

    Thanks.

  • July 24, 2008 at 4:55 am

    #847626

    TOP 1 ???

    According to what? You need a sort order to define TOP.

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

  • July 24, 2008 at 5:21 am

    #847636

    Tejwant Kaur (7/23/2008)

    then use a while loop (not a cursor) to do this.

    Not much difference.

    Tables are unordered sets. Without a column to define an order, there is no 'Top' record.

    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

  • July 24, 2008 at 7:45 am

    #847750

    Just curious, what value were you going to put in the 'top 1 aa value for each unique bb value'?

    If it was easy, everybody would be doing it!;)

  • July 24, 2008 at 7:55 am

    #847766

    In your test data there is no way to do a single update because any criteria you use will update all the aa with a distinct bb value. I think you would need a cursor to accomplish what you want. Which I assume is to only update 1 aa for each distinct bb so you want you final dataset to look like this:

    aa bb

    ---- -----------

    a 1

    1

    1

    c 2

    2

    2

    b 3

    3

    Is this correct?

    Jack Corbett
    Consultant - Straight Path Solutions
    Check out these links on how to get faster and more accurate answers:
    Forum Etiquette: How to post data/code on a forum to get the best help
    Need an Answer? Actually, No ... You Need a Question

Viewing 5 posts - 1 through 4 (of 4 total)

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