title: Add a group id column using row_number
body: <p>Please see the DDL below:</p>
  
  <pre><code>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')
  </code></pre>
  
  <p>I am looking for an output like this:</p>
  
  <p><a href="https://i.stack.imgur.com/nj2hK.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/nj2hK.png" alt="enter image description here"></a></p>
  
  <p>An ID of zero separates the groups.   I have tried this so far:</p>
  
  <pre><code>select * from (
  select  row_number() over (order by reference) as groupid, #test.* from #test
  </code></pre>
  
  <p>However, the groupid does not reset when a zero is reached in the ID column.</p>
  
  <p>I realize this is bad design.  The table was created by an external company.  I am just querying it.</p>