Tsql - get entire row information with max and group by

Question

I have a table, with the following columns:

PK1   PK2   ID   DATE    Value  flag

I do a calculation that involves taking the max value per ID.

  select id,
         max(value) 
    from table 
group by id

I want to mark the flag on the rows that I am using. If id and the max(value) correspond to multiple rows flag the one with the max date. If they have the same id,max(value) and max(date) flag exactly one of those rows (don't care which at that point)

Any ideas?

Thanks!

Answer 1

For SQL2005+ maybe something like this. (Assuming that "Mark" means update the flag column)

WITH cte AS
(
SELECT PK1, PK2, ID, DATE, Value, flag,
ROW_NUMBER() OVER (PARTITION BY id ORDER BY value desc, date desc) AS RN
FROM table 
)
UPDATE cte
SET flag=CASE WHEN RN=1 THEN 1 ELSE 0 END