Over Partition for set column SQL-CodePudding

I have this table, I need set ID column = 1 for the max value of column minutes, and the rest ID column = 0.

Initial table:

Register |minutes  | ID
10       |5        | 0
10       |6        | 0
10       |0        | 0     
12       |3        | 0
12       |0        | 0
12       |4        | 0

Final table:

Register |minutes  | ID
10       |5        | 0
10       |6        | 1
10       |0        | 0     
12       |3        | 0
12       |0        | 0
12       |4        | 1

Using Over Partition, any idea ?

UPDATE A
        SET ID = 1
        FROM
        (
            Select top 1 row_number() over (PARTITION BY     minutes 
            order by minutes asc)   AS column,*
            from table
        )A                  
        WHERE A.column=1

CodePudding user response：

Does this do what you want?

DECLARE @max INT

SELECT  TOP 1
        @max = Minutes
FROM    YourTable
ORDER BY Minutes DESC

UPDATE YourTable
SET     ID = CASE       
                 WHEN Minutes = @max
                 THEN 1
                 ELSE 0
             END

CodePudding user response：

You can use row_number() in an updatable CTE:

with m as (
  select *, 
    row_number() over(partition by register order by minutes desc) rn
  from t
)
update m set id=1 where rn=1

CodePudding user response：

If you don’t want to use CTE or variable tables:

UPDATE A
SET A.ID = CASE       
                 WHEN B.RowNumber = 1
                 THEN 1
                 ELSE 0
             END
FROM table A
JOIN (
   SELECT *, row_number() over (PARTITION BY Register 
      order by minutes DESC) AS RowNumber
   FROM table
) B ON A.Register = B.Register AND A.minutes = B.minutes