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