Log in  ::  Register  ::  Not logged in

 Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 How to update Flag for maximum amount in a a group Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, July 10, 2013 7:27 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, April 15, 2014 3:52 AM Points: 131, Visits: 248
 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 #TempHi, 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!
Post #1472120
 Posted Wednesday, July 10, 2013 7:31 AM
 SSCommitted Group: General Forum Members Last Login: Tuesday, April 08, 2014 6:13 AM Points: 1,694, Visits: 19,550
 `WITH CTE AS (SELECT Pk_Id,Lnno,Amount,Flag, ROW_NUMBER() OVER(PARTITION BY Lnno ORDER BY Amount DESC) AS rnFROM #Temp)UPDATE CTESET Flag='Y'WHERE rn=1;` ____________________________________________________How to get the best help on a forumhttp://www.sqlservercentral.com/articles/Best+Practices/61537Never approach a goat from the front, a horse from the rear, or a fool from any direction.
Post #1472124
 Posted Wednesday, July 10, 2013 7:33 AM
 Grasshopper Group: General Forum Members Last Login: Tuesday, January 14, 2014 1:35 PM Points: 22, Visits: 147
 `update #tempset flag = 'Y' from #tempjoin (select lnno ,max(amount) amount from #temp group by lnno) temp2 on #temp.lnno = temp2.lnno and #temp.amount = temp2.amount`
Post #1472131
 Posted Wednesday, July 10, 2013 7:53 AM
 SSC-Enthusiastic Group: General Forum Members Last Login: Tuesday, April 15, 2014 3:52 AM Points: 131, Visits: 248
 Thanks a lot!Its work for me.
Post #1472152

 Permissions