I worked it round to get 3 consecutive drops:
WITH C1(ID, DT, V) AS (
SELECT ID, ISO8601_DATE, MIN(INT_VALUE) FROM TABLE1
GROUP BY ID, ISO8601_DATE
)
SELECT DISTINCT A.ID
FROM C1 AS A
JOIN C1 AS B ON B.ID = A.ID AND B.DT = (SELECT MAX(X.DT) FROM C1 AS X
WHERE X.ID = A.ID AND X.DT > A.DT)
JOIN C1 AS C ON C.ID = A.ID AND C.DT = (SELECT MAX(X.DT) FROM C1 AS X
WHERE X.ID = A.ID AND X.DT < B.DT)
JOIN C1 AS D ON D.ID = A.ID AND D.DT = (SELECT MAX(X.DT) FROM C1 AS X
WHERE X.ID = A.ID AND X.DT < C.DT)
WHERE B.V < A.V AND C.V < B.V AND D.V < C.V
but I must have done something wrong as it picked 62 up from this:
62 2005-01-07 44
62 2006-02-01 47
62 2006-05-22 45
62 2007-04-05 45
62 2007-08-14 45
62 2008-05-21 46
62 2009-08-24 46
62 2010-10-08 45
62 2011-12-07 47
62 2013-01-17 46
62 2014-02-25 37
62 2015-03-30 39
62 2016-09-02 40
Any idea what I did wrong?
RBS
On Wed, Oct 19, 2016 at 11:23 PM, Bart Smissaert <bart.smissa...@gmail.com>
wrote:
> Ah, yes, sorry, I needed actually drops and was looking at that.
> Will work it round.
>
> RBS
>
>
> On Wed, Oct 19, 2016 at 11:06 PM, Petite Abeille <petite.abei...@gmail.com
> > wrote:
>
>>
>> > On Oct 19, 2016, at 11:40 PM, Bart Smissaert <bart.smissa...@gmail.com>
>> wrote:
>> >
>> > ID 2 for example shouldn't be selected.
>>
>> Hmmm? ID 2 has two ‘raises' as per your definition: 39 > 42 > 43 and 42 >
>> 43 > 45
>>
>> 2 2004-06-23 42
>> 2 2006-12-28 39
>> 2 2007-10-09 42
>> 2 2007-10-24 43
>> 2 2009-06-17 45
>> 2 2015-09-09 36
>>
>> Or?
>>
>> _______________________________________________
>> 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
