Given this list of values in SQL Server 2012:
1 2016-10-20 <-- THIS
1 2016-09-21 <-- (*)
One method doesn't use window functions at all:
select top 1 t.* from t cross join (select top 1 t2.id from t t2 order by t2.date desc) tt where t.date > ifnull((select max(t2.date) from t t2 where t2.id <> tt.id), '2000-01-01') order by t.date;
tt returns the
id on the most recent row in the table. The subquery in the
where clause selects the maximum date in the table for any other id. Then the comparison in the
where selects all the most recent records.
With window functions,
lag() might be the easiest:
select top 1 t.* from (select t.*, lag(status) over (order by date) as prev_status from t ) t where prev_status <> status or prev_status is null order by date desc;
where clause gets the rows where the status changes. The
top 1 and
order by date get the most recent time this has occurred.