Reputation: 1154
I've a table (Table) similar to this:
Date Value
20150201 5
20150205 6
20150206 0
20150207 5
20150208 0
20150209 9
20150219 7
I want to select row after the last "0" occurence with minimum date. In above example, the last 0 occured at date 20150208, so I want to select the row after it (one with date 20150209). Dates are distinct.
And, if for instance, there are no occurences with value "0", row with min Date should be selected. I'm using SQL Server 2008R2
. So, I couldn't use LEAD
and LAG
window functions here.
Upvotes: 2
Views: 43
Reputation: 1269623
You can get the last row with the zero value in SQL Server 2012+ by doing:
select top 1 t.*
from (select max(case when value = 0 then date end) over (order by date) as date0
from t
) t
where date > date0 or date0 is null
order by date;
In SQL Server 2008, you can use a correlated subquery:
select top 1 t.*
from t
where t.date > nullif((select max(date) from t t2 where t2.value = 0), '1900-01-01')
order by date;
Note: In this case, NULLIF()
is preferable to COALESCE()
because SQL Server has a particular poor implementation of COALESCE()
-- it evaluates the first argument twice.
Upvotes: 2