question

This is the 'islands' problem. For more detail see Itzik Ben-Gan's artcile : http://sqlmag.com/sql-server-2012/solving-gaps-and-islands-enhanced-window-functions declare @YourTable table (id int, val int); insert into @YourTable values ( 1 , 1 ), ( 2 , 1), ( 3 , 1), ( 4 , 0), ( 5 , 1), ( 6 , 1), ( 7 , 0), ( 8 , 1); with cte as ( select id, id - dense_rank()over (order by id) as grp from @YourTable where Val = 1 ) select min(id) as minid, max(id) as maxid from cte group by grp; returns minid maxid ----------- ----------- 1 3 5 6 8 8 (3 row(s) affected) ---- Now you've added user, simply add this to your grouping declare @YourTable table (id int, val int, [user] int); insert into @YourTable values ( 1 , 1,11), ( 2 , 1,11), ( 3 , 0,12), ( 4 , 1,13), ( 5 , 1,13), ( 6 , 0,13), ( 7 , 1,12), ( 8 , 1,13); with cte as ( select id, id - dense_rank()over (order by id) as grp, [user] from @YourTable where Val = 1 ) select min(id) as minid, max(id) as maxid, [User] from cte group by grp, [User] order by minid; returns minid maxid User ----------- ----------- ----------- 1 2 11 4 5 13 7 7 12 8 8 13 (4 row(s) affected)