Add group id column using row_number

Question

See below DDL:

create table #Test (reference int identity,id int,dataset varchar(10),primary key (reference))
insert into #Test (id,dataset) values (1,'System1')
insert into #Test (id,dataset) values (2,'System10')
insert into #Test (id,dataset) values (9,'System3')
insert into #Test (id,dataset) values (0,'')
insert into #Test (id,dataset) values (8,'System6')
insert into #Test (id,dataset) values (19,'System7')
insert into #Test (id,dataset) values (0,'')
insert into #Test (id,dataset) values (55,'System10')
insert into #Test (id,dataset) values (62,'System4')

I am looking for such a conclusion:

The zero identifier divides the groups. I have tried this so far:

select * from (
select  row_number() over (order by reference) as groupid, #test.* from #test

However, groupid does not reset when the ID column reaches zero.

I understand that this is a bad design. The table was created by an external company. I just ask him.

Answer 1

Try the following:

SELECT *,
       COALESCE(SUM(CASE WHEN id = 0 THEN 1 END) 
                OVER (ORDER BY reference),0) + 1 AS GroupID
FROM #Test

Used SUM OVERin a query with a query ORDER BYto calculate the current total number of values 0. If we add 1to this total, we get the required value GroupID.

. SUM SQL Server 2012 .

Edit:

SUM OVER ORDER BY SQL Server OUTER APPLY, :

SELECT t1.*,
       x.cnt + 1 AS GroupID
FROM #Test t1
OUTER APPLY (SELECT COUNT(cASE WHEN id = 0 THEN 1 END) AS cnt
             FROM #Test t2
             WHERE t2.reference <= t1.reference) AS x

Answer 2

SQL Server 2012 CTE. :

WITH CTE AS
(
  SELECT
      COALESCE(SUM(CASE WHEN ID = 0 THEN 1 END) OVER (ORDER BY reference),0)+ 1 AS GroupID,
      ID AS ID,
      dataset AS Dataset
  FROM 
     #Test 
)
SELECT * FROM CTE
WHERE ID !=0

Answer 3

SQL Server 2008 - outer apply. :

SELECT cume.GroupID, t.*
FROM #Test t OUTER APPLY
      (SELECT COUNT(*) as GroupID
       FROM #Test t2
       WHERE t2.reference <= t.reference AND t2.id = 0
      ) cume
WHERE t.id <> 0;

, OP , 0 . .

CTE, .