Group by same name of category but different meaning of category

Question

I have a table as below

start_date end_date category

26/05/2015 10:05 26/05/2015 10:10 d

26/05/2015 10:17 26/05/2015 10:20 d

26/05/2015 10:35 26/05/2015 10:44 c

26/05/2015 10:50 26/05/2015 10:57 c

26/05/2015 11:05 26/05/2015 11:13 d

26/05/2015 11:27 26/05/2015 11:30 d

and i need to group by category and take min(start_date) and max(end_date) but if there no sequence of category it has different meaning.

i would like to get result like this

start_date end_date category

26/05/2015 10:05 26/05/2015 10:20 d

26/05/2015 10:35 26/05/2015 10:57 c

26/05/2015 11:05 26/05/2015 11:30 d

thanks everyone

Answer 1

You can do this by assigning a group identifier to each group of successive rows, where the value is the same. I like to do this with a difference of row numbers:

select min(start_date) as start_date, max(end_date) as end_date, category
from (select t.*,
             (row_number() over (order by start_date) -
              row_number() over (partition by category order by start_date)
             ) as grp
      from table t
     ) t
group by category, grp;

Answer 2

Another option, adding an identity column and using a recursive CTE:

;with CTE (ID,st_date,end_date,category,group_no) as
(select ID,st_date,end_date,category,1 as group_no
 from dates d
 where ID = 1
 union all
 select d.ID,d.st_date,d.end_date,d.category
 ,case when d.category = cte.category then cte.group_no else cte.group_no + 1 end
 from dates d
 inner join CTE cte on d.ID - 1= cte.ID
 )

 select MIN(st_date) as st_date,MAX(end_date) as end_date,category from CTE
 group by group_no,category
 order by st_date asc

[http://sqlfiddle.com/#!3/261c3]