SQL Server - Update a column if row is the first r

问题:

How do i identify the first row that has value for each Brand and Category, then update Column "FirstValue" as 1, else 0? e.g of expected table

Date   |  Brands  |  Category |  Value  | FirstValue 
Jan 08 |   A      |  1        | 0       |0
Jan 08 |   A      |  2        | 0       |0
Jan 08 |   A      |  3        | 0       |0
Jan 08 |   B      |  1        | 12      |1
Jan 08 |   B      |  2        | 0       |0
Jan 08 |   B      |  3        | 0       |0
Feb 08 |   A      |  1        | 5       |1
Feb 08 |   A      |  2        | 0       |0
Feb 08 |   A      |  3        | 67      |1
Feb 08 |   B      |  1        | 0       |0
Feb 08 |   B      |  2        | 0       |0
Feb 08 |   B      |  3        | 6       |1

回答1:

Common table expressions are actually updatable, and combining CTEs with ranking functions ROW_NUMBER() gives a perfect solution:

with cte as (
 select row_number() over (partition by Brand, Category order by Date) as rn
, FirstValue
from Table)
update cte
set FirstValue = case when rn = 1 then 1 else 0 end;