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 |
CodePudding user response：
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;