SQL - categorize rows

Question

Below is the result set I am working with. What I would like is an additional column that identifies a X number of rows as the same. In my result set, rows 1-4 are the same (would like to mark as 1), rows 5-9 are the same (mark as 2); row 10 (mark as 3)

How is this possible using just SQL? I can't seem to do this using rank or dense_rank functions.

ranking              diff        bool
-------------------- ----------- -----------
1                    0           0
2                    0           0
3                    0           0
4                    0           0
5                    54          1
6                    0           0
7                    0           0
8                    0           0
9                    0           0
10                   62          1

LivingThing, row 5 is when the change occurred so row 5-9 should be marked as the same thing. — tad stones, Oct 08 '13 at 16:59
Correct it changed again at 6, but everything from the first occurrence of 1 till the next one should be categorized together. Hope that made sense — tad stones, Oct 08 '13 at 17:02
So this seems like a running total query summing the value of `bool` then adding one would give you the desired results? How many rows does this run over? — Martin Smith, Oct 08 '13 at 17:06

score 5 · Accepted Answer · edited May 23 '17 at 12:21

In general case you can do something like this:

select
    t.ranking, t.[diff], t.[bool],
    dense_rank() over(order by c.cnt) as rnk
from Table1 as t
    outer apply (
        select count(*) as cnt
        from Table1 as t2
        where t2.ranking <= t.ranking and t2.[bool] = 1
    ) as c

In your case you can do it even without dense_rank():

select
    t.ranking, t.[diff], t.[bool],
    c.cnt + 1 as rnk
from Table1 as t
    outer apply (
        select count(*) as cnt
        from Table1 as t2
        where t2.ranking <= t.ranking and t2.[bool] = 1
    ) as c;

Unfortunately, in SQL Server 2008 you cannot do running total with window function, in SQL Server 2012 it'd be possible to do it with sum([bool]) over(order by ranking).

If you have really big number of rows and your ranking column is unique/primary key, you can use recursive cte approach - like one in this answer, it's fastest one in SQL Server 2008 R2:

;with cte as
(
    select t.ranking, t.[diff], t.[bool], t.[bool] as rnk
    from Table1 as t
    where t.ranking = 1
    union all
    select t.ranking, t.[diff], t.[bool], t.[bool] + c.rnk as rnk
    from cte as c
        inner join Table1 as t on t.ranking = c.ranking + 1
)
select t.ranking, t.[diff], t.[bool], 1 + t.rnk
from cte as t
option (maxrecursion 0)

sql fiddle demo

SQL - categorize rows

1 Answers1