> On Oct 20, 2016, at 1:58 AM, Bart Smissaert <bart.smissa...@gmail.com> wrote: > > I worked it round to get 3 consecutive drops: > > but I must have done something wrong as it picked 62 up from this:
It has three consecutive drops, no? E.g. 47 < 46 < 37 Example: with DataSet as ( select 62 as key, '2005-01-07' as date, 44 as value union all select 62 as key, '2006-02-01' as date, 47 as value union all select 62 as key, '2006-05-22' as date, 45 as value union all select 62 as key, '2007-04-05' as date, 45 as value union all select 62 as key, '2007-08-14' as date, 45 as value union all select 62 as key, '2008-05-21' as date, 46 as value union all select 62 as key, '2009-08-24' as date, 46 as value union all select 62 as key, '2010-10-08' as date, 45 as value union all select 62 as key, '2011-12-07' as date, 47 as value union all select 62 as key, '2013-01-17' as date, 46 as value union all select 62 as key, '2014-02-25' as date, 37 as value union all select 62 as key, '2015-03-30' as date, 39 as value union all select 62 as key, '2016-09-02' as date, 40 as value ), NextSet ( 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.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 from NextSet where NextSet.count = 3 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