How do I auto-increment subsets in SSMS?

September 15, 2022

Consider a MWE with a table with four columns: ORDER_BASIS, GROUP_1, GROUP_2 and ORDER_VALUE. The first three are populated with data and I would like to now populate ORDER_VALUE with an integer (starting with 1 for each subset) that indicates the order of the ORDER_BASIS value for a given combination of GROUP_1 and GROUP_2. For example:

ORDER_BASIS	GROUP_1	GROUP_2	ORDER_VALUE
1.1	A	X	NULL
2.4	A	X	NULL
7.3	A	X	NULL
2.1	B	X	NULL
3.4	B	X	NULL
7.1	A	Y	NULL
8.4	A	Y	NULL
9.6	A	Y	NULL

should become:

ORDER_BASIS	GROUP_1	GROUP_2	ORDER_VALUE
1.1	A	X	1
2.4	A	X	2
7.3	A	X	3
2.1	B	X	1
3.4	B	X	2
7.1	A	Y	1
8.4	A	Y	2
9.6	A	Y	3

>Solution :

Assuming SSMS means you are using SQL Server, you can apply a row_number window function here in a derived table (or CTE) and directly update it:

update t set Order_Value = rn 
from (
    select *, Row_Number() over(partition by group_1, group_2 order by order_basis) rn
    from t
    where Order_Value is null
)t;