Hello,
On 2016-10-20 01:58, Bart Smissaert wrote:
I worked it round to get 3 consecutive drops:
[...]
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)
Probably you have forgotten to change a direction of the first comparison.
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?
The above mentioned JOIN was originally used to extract consecutive
ISO8601_DATE triplets, so you should not change MIN to MAX, nor reverse
comparison operators. If you want to have a quadruplets you should
append fourth JOIN without any changes besides C to D:
JOIN C1 AS D ON D.ID = A.ID AND D.DT = (SELECT MIN(X.DT) FROM C1
AS X
WHERE X.ID = A.ID AND X.DT > C.DT)
Optionally you can change MIN => MAX, ``>'' => ``<'' in JOIN (which
results in reversed quadruplets) and do NOT reverse comparison operators
in the final WHERE clause. You cannot both reverse tuplets and
comparison operators in the final WHERE clause because one operation
cancels an effect of other.
-- best regards
Cezary H. Noweta
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
