Thanks, will try that as well. RBS
On Thu, Oct 20, 2016 at 12:16 AM, Petite Abeille <petite.abei...@gmail.com> wrote: > > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
