Post reply

How to update Flag for maximum amount in a a group

  • July 10, 2013 at 7:27 am

    #295412

    Create Table #Temp

    (

    Pk_Id Int Identity(1,1),

    Lnno Int,

    Amount Int,

    Flag Char(1)

    )

    Insert Into #Temp(Lnno,Amount)

    Values (1,5),(1,10),(2,20),(2,25)

    Select * from #Temp

    Hi,

    My requirement is,I want to update Flag as 'Y' for the Lnno which has maximum amount.

    Please help this is urgent to me.

    Thanks in Advance!

  • July 10, 2013 at 7:31 am

    #1631209

    WITH CTE AS (

    SELECT Pk_Id,Lnno,Amount,Flag,

    ROW_NUMBER() OVER(PARTITION BY Lnno ORDER BY Amount DESC) AS rn

    FROM #Temp)

    UPDATE CTE

    SET Flag='Y'

    WHERE rn=1;

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • July 10, 2013 at 7:33 am

    #1631215

    update #temp

    set flag = 'Y'

    from #temp

    join (select lnno ,max(amount) amount from #temp group by lnno) temp2 on

    #temp.lnno = temp2.lnno

    and #temp.amount = temp2.amount

  • July 10, 2013 at 7:53 am

    #1631232

    Thanks a lot!

    Its work for me.

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

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