Search code examples
sqlsql-serversubsetauto-increment

How do I auto-increment subsets?


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;