CODEWITHSUNDEEP
sqlsql-servergroup-bysequencerow-number
Chelsea
Chelsea

Reputation: 11

How to update a column with incrementally sequenced values that change depending on other column value

I am trying to update a column in a table so that the Index column (which is currently arbitrary numbers) is renumbered sequentially starting at 1000 with increments of 10, and this sequence restarts every time the Group changes.

I have tried ROWNUMBER() with PARTITION and trying to define a SEQUENCE, but I can't seem to get the result I'm looking for.

Table 1

ID    Group    Index
1         A       1
2         A       2
3         B       3
4         B       4
5         B       5
6         C       6
7         D       7

What I want: Table 1

ID    Group       Index
1         A       1000
2         A       1010
3         B       1000
4         B       1010
5         B       1020
6         C       1000
7         D       1000

Upvotes: 1

Views: 35

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269743

You can use row_number() with some arithmetic:

select t.*,
       990 + 10 * row_number() over (partition by group order by id) as index
from t;

Note that group and index are SQL reserved words, so they are really bad column names.

Upvotes: 1

Related Questions