SQL Group by date with multiple same results

Question

How to write a statement that groups data by date but shows when data changed for the first time

date - when data changed

a,b,c - some data and it can be anything

date,a,b,c
04/26/2008,1,1,1
04/25/2008,1,2,1
04/24/2008,1,1,1
04/23/2008,1,1,1
04/22/2008,1,1,1
04/21/2008,2,2,2
04/20/2008,1,1,1

This is should be the result. It might have the same data on different dates, but it missing the next day when data stayed the same.

04/26/2008,1,1,1
04/25/2008,1,2,1
04/22/2008,1,1,1
04/21/2008,2,2,2
04/20/2008,1,1,1

It should work on MS SQL Server 2008 r2

Answer 1

; with cte as 
(select *,
row_number() over (partition by a,b,c order by date asc) as rn
,row_number() over (order by date asc) as rn1
from yourtablename
)
select min(date) date,a,b,c from cte group by (rn1-rn),a,b,c
order by min(date) desc

Check below link

For reference click here

Answer 2

This is like a gaps and islands problem.

 create table yourtablename ([date] date,a int,b int,c int);
insert into yourtablename values
 ('04/26/2008',1,1,1)
,('04/25/2008',1,2,1)
,('04/24/2008',1,1,1)
,('04/23/2008',1,1,1)
,('04/22/2008',1,1,1)
,('04/21/2008',2,2,2)
,('04/20/2008',1,1,1);

; with cte as 
(
select date, a,b,c,combinedstring=cast(a as varchar(max))+ cast(b as varchar(max))+ cast(c as varchar(max))
from yourtablename 
)
,cte2 as 
(select *,
rn1=row_number() over (partition by combinedstring order by date asc)
,rn2= row_number() over (order by date asc) 
from cte
)
select y.* 
from 
(
select date=min(date)
from cte2
group by (rn2-rn1),combinedstring) t
join yourtablename y
on t.date=y.date