Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to update Flag for maximum amount in a a group Expand / Collapse
Author
Message
Posted Wednesday, July 10, 2013 7:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:20 PM
Points: 132, Visits: 255


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!
Post #1472120
Posted Wednesday, July 10, 2013 7:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, September 16, 2014 7:45 AM
Points: 1,678, Visits: 19,552
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;



____________________________________________________

How to get the best help on a forum

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

Never 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

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, January 14, 2014 1:35 PM
Points: 22, Visits: 147
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

Post #1472131
Posted Wednesday, July 10, 2013 7:53 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Sunday, September 7, 2014 11:20 PM
Points: 132, Visits: 255
Thanks a lot!

Its work for me.
Post #1472152
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse