Update column based on min value

Transact-SQL
1

Need help with the sql

Emp table
Id,codes,flag
1,234,null
1,345,null
2,765,null
3,873,null
4,543,null
4,980,null

So basically when count(Id) >1 I want to set the lowest codes (min) to 1 set the max codes to 0
And all others Id With count of 1 automatic set to 1

Emp table
Id,codes,flag
1,234,1
1,345,0
2,765,1
3,873,1
4,543,1
4,980,0

2 
SELECT Id, codes, CASE WHEN row_num = 1 THEN 1 ELSE 0 END AS flag
FROM (
    SELECT Id, codes, ROW_NUMBER() OVER(PARTITION BY Id ORDER BY codes) AS row_num
    FROM dbo.your_table_name
) AS query1
ORDER BY Id, codes
1 Like
3

What happens when yu have a scenario like this with null values

Id,codes,flag
1,null,null
1,543,null
2,875,null
2,612,null
3,777,null
4,543,null

Now I want to set null codes to

Id,codes,flag
1,null,0
1,543,1
2,875,0
2,612,1
3,777,1
4,543,1

4

Since NULL sorts lower than any other value, it will be set as 1 by the code. You had never mentioned NULL before now, nor how you'd want to set the corresponding flag value for NULL if one existed.