Alter SQL column based on comparison of number of elements

Question

I have a table with columns (Id, Prod, Id) and I must create a (Flag) like this:

Id Prod Id2 Flag
1  A    11  0
2  A    11  1
3  A    11  1
4  B    12  0
5  B    12  1
6  A    13  0
7  B    14  0
8  D    15  0

This (Flag) is created by grouping by (Id2) and assigning 0 for the lowest (Id) and 1 otherwise.

I tried using group by to no avail. How can I do that?

Answer 1

You can use a case expression with a windowed function:

select tbl.*
    , case
        when row_number() over (partition by Id2 order by Id) = 1 then 0
        else 1
      end Flag
from tbl

Example Fiddle

You didn't tag a DBMS, so I've gone with SQL Server, but this should work for most.