List the status change's rows
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
declare @t table (id int, status char(1), UpdateDate datetime, primary key(id,updatedate))
insert into @t
select 1,'A','2010-2-1' UNION ALL
select 2,'B','2010-2-3' UNION ALL
select 1,'A','2010-2-4' UNION ALL
select 2,'C','2010-2-5' UNION ALL
select 1,'A','2010-2-7' UNION ALL
select 3,'B','2010-2-8' UNION ALL
select 1,'B','2010-2-9' union all
select 2,'B','2010-2-10' union all
select 3,'B','2010-2-11'
SELECT * FROM @T
SELECT id,Status,updatedate
FROM @T a
where exists (select * from @t where a.id = id and a.status <> status and updatedate = (select max(updatedate) from @t where id = a.id and updatedate < a.updatedate))
or exists (select * from @t where a.id = id and a.status <> status and updatedate = (select min(updatedate) from @t where id = a.id and updatedate > a.updatedate))
insert into @t
select 1,'A','2010-2-1' UNION ALL
select 2,'B','2010-2-3' UNION ALL
select 1,'A','2010-2-4' UNION ALL
select 2,'C','2010-2-5' UNION ALL
select 1,'A','2010-2-7' UNION ALL
select 3,'B','2010-2-8' UNION ALL
select 1,'B','2010-2-9' union all
select 2,'B','2010-2-10' union all
select 3,'B','2010-2-11'
SELECT * FROM @T
SELECT id,Status,updatedate
FROM @T a
where exists (select * from @t where a.id = id and a.status <> status and updatedate = (select max(updatedate) from @t where id = a.id and updatedate < a.updatedate))
or exists (select * from @t where a.id = id and a.status <> status and updatedate = (select min(updatedate) from @t where id = a.id and updatedate > a.updatedate))