A question from T-SQL Forum MSDN: Grouping of rows, every second then every third


https://social.msdn.microsoft.com/Forums/en-US/def432c4-0e62-44b7-ba13-d0444ed39511/grouping-of-rows-every-second-then-every-third?forum=transactsql

  
 
 --Naomi's setup and solution
--option 1

 declare @t table (id int identity(1,1) primary key, dataCol varchar(10))
insert into @t (dataCol) values ('a'),('b'),('c'),('d'),('e'),('f'),('g'),('h'),('i'),('j'),('k')

;with cte as (

select *, ROW_NUMBER() over (order by id) - ROW_NUMBER() over (partition by case when id % 5 in (1,2) then 1 else 2 end order by id) as [Grp]
from @t)

SELECT Id, DataCol, dense_rank() over (order by [Grp]) as GrpNumber

from cte  order by Id
   
  
--option 2

  --Here is mine

select id, dataCol ,Sum(val) Over(Order by id)  from  @t   
cross apply (values (Case when id%5 in (1,3) then  1 else 0 end)) d(val)
   
 --better one

--option 3

select id, dataCol ,Sum((Case when id%5 in (1,3) then  1 else 0 end)) Over(Order by id)  from  @t   
 

 
Advertisements


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s