Jonas Jonas - 1 year ago 61
SQL Question

Find latest date in groups of rows

Given this list of values in SQL Server 2012:

Status Date
------ -----------
1 2016-12-01
1 2016-11-02
1 2016-10-20 <-- THIS
2 2016-10-01
1 2016-09-21 <-- (*)
3 2016-08-15


(*) Don't need this one because there is a "status 2" row between the sequence

I need to get the latest date of the list, but if there is a group of same status first, I need to return the minimum date of them. What is the best way to do it?

Answer Source

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;

The subquery 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;

The where clause gets the rows where the status changes. The top 1 and order by date get the most recent time this has occurred.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download