Geek Answers Handbook

Group by numbers in sequence

I have several such data:

row id 1 1 2 36 3 37 4 38 5 50 6 51

I would like to request it like this:

  row id group 1 1 1 2 36 2 3 37 2 4 38 2 5 50 3 6 51 3

... so that I can GROUP BY, where the numbers are sequentially consecutive.

Also, the loop / cursor is out of the question since I am working with a rather large dataset, thanks.

+4
source share
5 answers
 create table #temp ( IDUnique int Identity(1,1), ID int, grp int ) Insert into #temp(ID) Values(1) Insert into #temp(ID) Values(36) Insert into #temp(ID) Values(37) Insert into #temp(ID) Values(38) Insert into #temp(ID) Values(50) Insert into #temp(ID) Values(51) declare @IDUnique int declare @PreviousUnique int declare @ID int declare @grp int declare @Previous int declare @Row int DECLARE @getAccountID CURSOR SET @getAccountID = CURSOR FOR SELECT Row_Number() Over(Order by IDUnique) Row, IDUnique, ID From #temp OPEN @getAccountID FETCH NEXT FROM @getAccountID INTO @Row, @IDUnique, @ID WHILE @@FETCH_STATUS = 0 BEGIN IF(@Row = 1) Begin update #temp set grp = 1 Where IDUnique = @IDUnique set @Previous = @ID set @grp = 1 End Else If (@Previous + 1 = @ID) Begin update #temp set grp = @grp Where IDUnique = @IDUnique set @Previous = @ID End Else Begin set @Previous = @ID set @grp = @grp + 1 update #temp set grp = @grp Where IDUnique = @IDUnique End FETCH NEXT FROM @getAccountID INTO @Row, @IDUnique, @ID END CLOSE @getAccountID DEALLOCATE @getAccountID Select * from #temp Drop Table #temp
+1
source 
 with data(row, id) as ( select * from ( values (1,1) ,(2,36) ,(3,37) ,(4,38) ,(5,50) ,(6,51) ) as foo(row, id) ), anchor(row, id) as ( select row, id from data d1 where not exists(select 0 from data d2 where d2.id = d1.id - 1) ) select d1.*, dense_rank() over(order by foo.id) as thegroup from data d1 cross apply (select max(id) from anchor where anchor.id <= d1.id) as foo(id) order by d1.row ;
+4
source 
 ;WITH firstrows AS ( SELECT id, ROW_NUMBER() OVER (ORDER BY id) groupid FROM Table1 a WHERE id - 1 NOT IN (SELECT b.id FROM Table1 b) ) SELECT id, ( SELECT MAX(b.groupid) FROM firstrows b WHERE b.id <= a.id ) groupid FROM Table1 a
+3
source

This solution does more work, which is strictly necessary on the grounds that there may be gaps in the sequence of row values ​​and on the assumption that these gaps should be ignored.

Test data setup:

 DECLARE @table TABLE (ROW INT, id INT ) INSERT @table SELECT 1,1 UNION SELECT 2,36 UNION SELECT 3,37 UNION SELECT 4,38 UNION SELECT 5,50 UNION SELECT 6,51

Output request

 ;WITH grpCTE AS ( SELECT ROW, id, ROW_NUMBER() OVER (ORDER BY ROW ) AS rn FROM @table ) ,recCTE AS ( SELECT ROW, id, rn, 1 AS grp FROM grpCTE WHERE rn = 1 UNION ALL SELECT g.row, g.id, g.rn, CASE WHEN g.id = r.id + 1 THEN r.grp ELSE r.grp + 1 END AS grp FROM grpCTE AS g JOIN recCTE AS r ON g.rn = r.rn + 1 ) SELECT row, id, grp FROM recCTE
+1
source 
  Select T.Id, T.Row, groupId as "Group", dr FROM tbrows T Left Outer Join ( Select min(id) as groupId,DENSE_RANK() over( order by min(id)) as dr, MIN(row-id) as d, Sum(1) as s FROM tbrows Group BY (row-id) ) U On (T.Id >= U.groupId) and (T.Id < U.groupId+Us) Order By T.Id
+1
source

More articles:


All Articles