question

Sagar_Thakur avatar image
Sagar_Thakur asked

How to update table in below with max function

How to update table with max function and flag should be marked as 'M' only one row.




sql server 2016
capture.png (4.0 KiB)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

It is updating 2 two rows but i want only one row should be update.


UPDATE t

SET flag = 'm'

FROM test t

INNER JOIN (

SELECT companyid, MAX(shipment_count) as shipment_count

FROM test

GROUP BY companyid

) m

ON t.shipment_count = m.shipment_count

0 Likes 0 ·

1 Answer

· Write an Answer
anthony.green avatar image
anthony.green answered

You will need to use row_number and a CTE to do this

with cte as
( select row_number() over (order by shipment_count desc) as rn, * from test )
update cte set flag = 'm' where rn = 1
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.