Return max value and associted row values with the same ID

Transact-SQL
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;
1 Like
3

cool that works thanks

4

Want to thank JamesK. Similar problem and this gave me the idea I needed to get what I wanted.

5

hi

another way is

Top 1
order by TransID Desc

the reason for choosing this approach ... !!! performance , less code etc etc etc