Label groups in SQL records based on consecutive matching records

user_2263937 0
Viewed 99

Given the below dataset

CREATE TABLE #temp
 ( A NUMERIC,
   B NUMERIC )

INSERT INTO #temp VALUES (243184, 0);
INSERT INTO #temp VALUES (240719, 0);
INSERT INTO #temp VALUES (236482, 1);
INSERT INTO #temp VALUES (230777, 0);
INSERT INTO #temp VALUES (226023, 0);
INSERT INTO #temp VALUES (222522, 0);
INSERT INTO #temp VALUES (214977, 1);

SELECT *
  FROM #temp
 ORDER BY A DESC

A                                       B
--------------------------------------- ---------------------------------------
243184                                  0
240719                                  0
236482                                  1
230777                                  0
226023                                  0
222522                                  0
214977                                  1

How can I obtain the following output?

A                                       B                                       C
--------------------------------------- --------------------------------------- ----
243184                                  0                                       1
240719                                  0                                       1
236482                                  1                                       2
230777                                  0                                       3
226023                                  0                                       3
222522                                  0                                       3
214977                                  1                                       4

I want to group consecutive instances of [B] records into groups based on the value of 'A' when sorted in descending value.

So that each time [B] flips from 0 to 1 a new group starts

Any ideas?

sql-server-2014 tsql
1 Answers

Please try this:

SELECT s.A, s.B
    ,SUM(s.IsChange) OVER (ORDER BY s.A DESC ROWS UNBOUNDED PRECEDING) +1 AS [C]
FROM (
    SELECT t.A,t.B
        ,CASE WHEN t.B <> LEAD(t.B)OVER(ORDER BY t.A) THEN 1 ELSE 0 END AS [IsChange]
    FROM #temp t
) s
ORDER BY s.A DESC
;
user_41195990
Related