Similar to this : T-SQL incrementing counter based on change in a column value
I have this table :
ID | VALUE
5 | A
6 | A
6 | A
6 | B
6 | A
6 | B
6 | B
6 | A
6 | C
The desired result is to have this :
ID | VALUE | GROUP
5 | A | 1
6 | A | 2
6 | A | 2
6 | B | 3
6 | A | 4
6 | B | 4
6 | B | 4
6 | A | 5
6 | C | 6
A new column (GROUP) with a value that increments everytime the first two columns change, but doesn't increment when the first two columns values are the same as the previous line.
So far, with the RANK OVER() function, the best I can achieve is this :
ID | VALUE | GROUP
5 | A | 1
6 | A | 2
6 | A | 2
6 | B | 3
6 | A | 2
6 | B | 3
6 | B | 3
6 | A | 1
6 | C | 4
Let me assume that you have a column called ordering
that specifies the ordering of the records. SQL tables represent unordered sets, so there is no "natural" ordering to the rows.
You can do what you want with a difference of row numbers and then some additional manipulation:
select id, value, dense_rank() over (order by grp) as grouping
from (select t.*,
min(ordering) over (partition by id, value, seqnum - seqnum_iv) as grp
from (select t.*,
row_number() over (order by ordering) as seqnum,
row_number() over (partition by id, value order by ordering) as seqnum_iv
from t
) t
) t;
The innermost subquery calculates to sequences of values. I encourage you to look at the results to see what is happening. The difference is constant for each of your "islands".
The second gets the minimum of the ordering
value for each group. This can then be used to assign a sequential value as the ultimate result.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With