> It has three consecutive drops, no? E.g. 47 < 46 < 37 That is 3 values, but only 2 drops.
RBS On Thu, Oct 20, 2016 at 1:07 AM, Petite Abeille <petite.abei...@gmail.com> wrote: > > > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
