Trouble with Group By Sequence Numbers

Question

I am basically after sql that will do the following. I am after a sequential set of numbers in relation to when column B changes but keeping column ID in sequence I have tried Dense Rank but it always throws out the sequence of the ID column which messes what I am after. Any ideas?

ID||A||B
01||T||1
02||T||1
03||T||0
04||T||0
05||T||1
06||T||1
07||T||1
08||T||0
09||T||1
10||T||1
11||T||0

INTO

ID||A||B||C
01||T||1||1
02||T||1||1
03||T||0||2
04||T||0||3
05||T||1||4
06||T||1||4
07||T||1||4
08||T||0||5
09||T||1||6
10||T||1||6
11||T||0||7

Answer 1

--#3: Running total of value changes
select id, a, b
    ,sum(has_changed) over (partition by A order by id
        rows between unbounded preceding and current row) c
from
(
    --#2: Find rows where the value changed.
    select id, a, b
        ,case
            when b = lag(b) over (partition by A order by id) then 0
            else 1
        end has_changed    
    from
    (
        --#1: Test data
        select '01' ID, 'T' A, 1 B from dual union all
        select '02' ID, 'T' A, 1 B from dual union all
        select '03' ID, 'T' A, 0 B from dual union all
        select '04' ID, 'T' A, 0 B from dual union all
        select '05' ID, 'T' A, 1 B from dual union all
        select '06' ID, 'T' A, 1 B from dual union all
        select '07' ID, 'T' A, 1 B from dual union all
        select '08' ID, 'T' A, 0 B from dual union all
        select '09' ID, 'T' A, 1 B from dual union all
        select '10' ID, 'T' A, 1 B from dual union all
        select '11' ID, 'T' A, 0 B from dual
    ) test_data
)
order by id;

Results:

ID  A   B   C
01  T   1   1
02  T   1   1
03  T   0   2
04  T   0   2
05  T   1   3
06  T   1   3
07  T   1   3
08  T   0   4
09  T   1   5
10  T   1   5
11  T   0   6

Not exactly the same, although I think your example has an extra increment, as @Adam Hawkes pointed out.

---

UPDATE

This will generate your expected results:

--#3: Running total of value changes, or where the value is 0
select id, a, b
    ,sum(has_changed_or_0) over (partition by A order by id
        rows between unbounded preceding and current row) c
from
(
    --#2: Find rows where the value changed, or where value is 0
    select id, a, b
        ,case
            when b = 0 then 1
            when b = lag(b) over (partition by A order by id) then 0
            else 1
        end has_changed_or_0
    from
    (
        --#1: Test data
        select '01' ID, 'T' A, 1 B from dual union all
        select '02' ID, 'T' A, 1 B from dual union all
        select '03' ID, 'T' A, 0 B from dual union all
        select '04' ID, 'T' A, 0 B from dual union all
        select '05' ID, 'T' A, 1 B from dual union all
        select '06' ID, 'T' A, 1 B from dual union all
        select '07' ID, 'T' A, 1 B from dual union all
        select '08' ID, 'T' A, 0 B from dual union all
        select '09' ID, 'T' A, 1 B from dual union all
        select '10' ID, 'T' A, 1 B from dual union all
        select '11' ID, 'T' A, 0 B from dual
    ) test_data
)
order by id;