> and do NOT reverse comparison operators in the final WHERE clause. Yes, thank, that was it.
RBS On Thu, Oct 20, 2016 at 4:40 AM, Cezary H. Noweta <c...@poczta.onet.pl> wrote: > 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 > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
