SQLTeam.com | Weblogs | Forums

Return max value and associted row values with the same ID


#1

I would like to get the max Trans ID and its associated values within that row

example
TransID ID AMOUNT
20, 454 , 54.22
10, 454, 321.00
12, 454, 2314.00
4, 454, 210.00

My Query should return
TransID ID AMOUNT
20, 454, 54.22 (Only the max TRANSID and values are returned not all even thoughthey all have the same ID's )


#2
;with cte as
( select TransId, ID, AMOUNT,
   row_number() over (partition by ID order by TransId DESC) as RN
FROM YourTable
)
select TransId, ID, AMOUNT
FROM cte
WHERE RN = 1;

#3

cool that works thanks