SQL - Group rows via criteria until exception is found

Question

I am trying to add a Group column to a data set based on some criteria. For a simple example:

╔════╦══════╗
║ ID ║ DATA ║
╠════╬══════╣
║  1 ║   12 ║
║  2 ║   20 ║
║  3 ║    3 ║
║  4 ║   55 ║
║  5 ║   11 ║
╚════╩══════╝

Let's say our criteria is that the Data should be greater than 10. Then the result should be similar to:

╔════╦══════╦═══════╗
║ ID ║ DATA ║ GROUP ║
╠════╬══════╬═══════╣
║  1 ║   12 ║     1 ║
║  2 ║   20 ║     1 ║
║  3 ║    3 ║     2 ║
║  4 ║   55 ║     3 ║
║  5 ║   11 ║     3 ║
╚════╩══════╩═══════╝

So, all the rows that satisfied the criteria until an exception to the criteria occurred became part of a group. The numbering of the group doesn't necessarily need to follow this pattern, I just felt like this was a logical/simple numbering to explain the solution I am looking for.

Answer 1

You can calculate the group identifier by finding each row where data <= 10. Then, the group identifier is simply the number of rows where that condition is true, before the given row.

select t.*,
       (select count(*)
        from t t2
        where t2.id <= t.id and
              t2.data <= 10
       ) as groupId
from t;

SQL Server 2012 has cumulative sum syntax. The statement would be simpler in that database:

select t.*,
       sum(case when t2.data <= 10) over (order by id) as groupId
from t;

EDIT:

The above does not take into account that the values less than 10 are in their own group. The logic above is that they start a new group.

The following assigns a group id with this constraint:

select t.*,
       ((select 2*count(*)
         from t t2
         where t2.id < t.id and
               t2.data <= 10
        ) + (case when t.id <= 10 then 1 else 0 end)
       ) as groupId
from t;

Answer 2

This can be done easily with a recursive query:

;WITH CTE 
     AS (SELECT *, 
                1 AS [GROUP] 
         FROM   TABLEB 
         WHERE  ID = 1 
         UNION ALL 
         SELECT T1.ID, 
                T1.DATA, 
                CASE 
                  WHEN T1.DATA < 10 THEN T2.[GROUP] + 1 
                  ELSE T2.[GROUP] 
                END [GROUP] 
         FROM   TABLEB T1 
                INNER JOIN CTE T2 
                        ON T1.ID = T2.ID + 1) 
SELECT * 
FROM   CTE 

A working example can be found on SQL Fiddle.

Good Luck!