Partition data into subgroups based on bit fields

I have the first 4 columns of data, and I wan't to use the Ranking functions in the SQL 2008 R2 to derive the fifth column. What's the best way to partition the data into subgroups based on the nextiteminsubgroup and previousiteminsubgroup fields?

 Group  OrderInGroup    NextItemInSubGroup  PreviousItemInSubGroup  SubGroup
    1   1   1   0   1
    1   2   1   1   1
    1   3   1   1   1
    1   4   0   1   1
    1   5   0   0   2
    1   6   0   0   3
    1   7   1   0   4
    1   8   1   1   4
    1   9   0   1   4
  开发者_如何学Python  2   1   0   0   1
    2   2   0   0   2
    2   3   0   0   3
    2   4   1   0   4
    2   5   0   1   4
    3   1   0   0   1
    4   1   0   0   1
    4   2   0   0   2
    4   3   0   0   3

---

A recursive CTE solution:

DECLARE @t TABLE
([Group]  INT 
,OrderInGroup  INT
,NextItemInSubGroup INT
,PreviousItemInSubGroup INT
,SubGroup INT
)

INSERT @t
VALUES
(1,1,1,0,1),(1,2,1,1,1),(1,3,1,1,1),(1,4,0,1,1),(1,5,0,0,2),(1,6,0,0,3),
(1,7,1,0,4),(1,8,1,1,4),(1,9,0,1,4),(2,1,0,0,1),(2,2,0,0,2),(2,3,0,0,3),
(2,4,1,0,4),(2,5,0,1,4),(3,1,0,0,1),(4,1,0,0,1),(4,2,0,0,2),(4,3,0,0,3)

;WITH recCTE
AS
(
    SELECT  [Group], OrderInGroup,NextItemInSubGroup , PreviousItemInSubGroup, 1 AS subgroup
    FROM @t
    WHERE OrderInGroup = 1

    UNION ALL

    SELECT  r.[Group], t.OrderInGroup,t.NextItemInSubGroup , t.PreviousItemInSubGroup, 
            CASE WHEN r.NextItemInSubGroup = 1 THEN r.subgroup ELSE r.subgroup + 1 END
    FROM recCTE AS r
    JOIN @t AS t
    ON t.[Group] = r.[Group]
    AND t.OrderInGroup = r.OrderInGroup + 1
)   
SELECT * FROM recCTE
ORDER BY [Group],OrderInGroup ;

P.S. it's best practice to avoid using SQL keywords (e.g. GROUP) as table/column names

---

Seems like 0 and 0 restart the ranking.

Select
      Rank() Over ( 
        Partition By 
          [Group]
        , Case When [NextItemInSubGroup] + [PreviousItemInSubGroup] = 0
               Then 0
               Else 1
          End
        Order By [OrderInGroup]
      ) as [SubGroup]
From Your_Table;