> On Oct 20, 2016, at 12:23 AM, Bart Smissaert <bart.smissa...@gmail.com> wrote:
>
> Ah, yes, sorry, I needed actually drops and was looking at that.
Just for fun, here is one using recursion:
with
DataSet
as
(
select 1 as id, 2 as key, '2004-06-23' as date, 42 as value union all
select 2 as id, 2 as key, '2006-12-28' as date, 39 as value union all
select 3 as id, 2 as key, '2007-10-09' as date, 42 as value union all
select 4 as id, 2 as key, '2007-10-24' as date, 43 as value union all
select 5 as id, 2 as key, '2009-06-17' as date, 45 as value union all
select 6 as id, 2 as key, '2015-09-09' as date, 36 as value
),
NextSet
(
id,
key,
date,
value,
next_date,
count
)
as
(
select DataSet.*,
(
select Self.date
from DataSet as Self
where Self.key = DataSet.key
and Self.date > DataSet.date
order by Self.date
limit 1
) as next_date,
0 as count
from DataSet
union all
select NextSet.id as id,
NextSet.key as key,
NextSet.date as date,
NextSet.value as value,
(
select Self.date
from DataSet as Self
where Self.key = NextSet.key
and Self.date > NextSet.next_date
order by Self.date
limit 1
) as next_date,
NextSet.count + 1 as count
from NextSet
where exists
(
select 1
from DataSet
where DataSet.key = NextSet.key
and DataSet.date = NextSet.next_date
and DataSet.value > NextSet.value
)
)
select NextSet.key,
min( NextSet.id ) as min_id,
max( NextSet.id ) as max_id
from NextSet
where NextSet.count = 2
group by NextSet.key
order by 1;
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
