Add flag on SQL Server table, based on pair of features

Question

I have a table in SQL Server with Id, Code and Prod. I want to add a flag and end up like:

Id Code Prod Flag
0  101  A    0
1  101  B    0
2  101  A    1
3  101  A    1
4  101  B    1
5  160  B    0
6  160  A    0
7  160  B    1
8  190  A    0
9  190  B    0

For each pair Code-Prod (eg. 101-A, which has 3 cases), the flag should assume 0 for the first Id, 1 otherwise.

How do I do that?

Answer 1

This is ideally solved with row_number() window function, combined with an updatable CTE:

with upd as (
  select *, Row_Number() over(partition by Concat(code,prod) order by id) rn
  from t
)
update upd 
set flag = case when rn = 1 then 0 else 1 end;