Looks very nice and a lot faster, but I get more ID's than I should. With this data for example:
ID ISO8601_DATE INT_VALUE -------------------------------------------- 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 3 2004-05-05 46 3 2005-05-24 43 3 2007-05-14 43 3 2010-04-16 45 3 2010-11-22 43 3 2014-09-15 37 8 2004-06-10 45 10 2005-06-10 46 12 2004-09-07 49 12 2005-02-09 50 12 2006-09-04 46 12 2007-02-13 49 12 2007-04-24 46 12 2007-05-15 47 12 2007-06-05 46 12 2007-07-17 46 12 2007-10-23 49 12 2008-09-29 46 12 2009-01-27 47 12 2009-04-23 45 12 2010-03-26 48 12 2010-06-09 47 12 2011-06-23 43 12 2013-03-19 47 12 2014-03-12 38 12 2014-12-19 40 12 2016-02-01 39 13 2005-01-25 43 13 2005-09-15 45 13 2006-09-26 45 13 2006-10-23 42 13 2008-11-03 44 13 2009-05-18 45 13 2010-03-08 44 13 2010-10-12 45 13 2011-11-07 45 13 2013-03-06 44 13 2013-11-20 36 13 2014-09-18 34 13 2014-09-26 34 13 2015-06-16 36 13 2015-08-06 35 13 2015-10-23 34 13 2016-08-08 35 14 2016-04-01 38 21 2006-04-05 46 21 2008-08-07 46 22 2005-06-29 45 22 2005-07-08 42 22 2005-07-15 42 22 2005-07-29 43 22 2007-01-29 44 22 2007-04-25 44 22 2012-06-01 44 22 2015-04-01 39 24 2010-05-28 49 24 2014-07-04 40 26 2004-09-24 46 26 2004-10-15 46 26 2004-11-23 45 26 2005-09-27 45 26 2006-12-04 46 26 2007-10-08 46 26 2008-11-10 46 26 2009-11-13 46 26 2010-10-13 39 26 2010-10-22 42 26 2010-11-03 43 26 2010-12-02 45 26 2011-11-02 47 26 2012-10-22 46 26 2013-11-04 39 26 2014-06-06 38 I get these ID's: ID ---------------------------- 2 13 22 26 ID 2 for example shouldn't be selected. RBS On Wed, Oct 19, 2016 at 9:48 PM, R Smith <rsm...@rsweb.co.za> wrote: > > > On 2016/10/19 4:53 PM, Bart Smissaert wrote: > >> Have a table like this: >> >> create table TABLE1([ID] INTEGER, [ISO8601_DATE] TEXT, [INT_VALUE] >> INTEGER) >> with data like this: >> >> ID ISO8601_date INT_VALUE >> ---------------------------------------------------- >> 1 2016-01-01 10 >> 1 2016-01-28 9 >> 1 2016-03-05 12 >> 1 2016-05-12 11 >> 2 2016-01-01 12 >> 2 2016-02-02 10 >> 2 2016-03-05 12 >> 2 2016-04-07 14 >> >> The date column is in the format yyyy-mm-dd. >> >> Now I want to select the unique ID values that have 2 consecutive rises in >> INT_VALUE. >> A rise will need to be a higher value on the next date, but not on the >> same >> date. >> So in the above data the result would be 2 only as that has 2 rises on >> consecutive dates. >> >> Any suggestions how this can be done? >> > > First get the dates to be unique so a single date isn't considered twice, > then match next higher and next higher dates to get 2 consecutive items, > filter on values going consecutively up. > > Like so: > > -- SQLite version 3.9.2 [ Release: 2015-11-02 ] on SQLitespeed > version 2.0.2.4. > > -- Script Items: 5 Parameter Count: 0 > -- 2016-10-19 22:40:25.512 | [Info] Script Initialized, > Started executing... > -- > =========================================================== > ===================================== > > create table table1([ID] INTEGER, [ISO8601_date] TEXT, [INT_value] > INTEGER) > > INSERT INTO table1 VALUES > (1 ,'2016-01-01', 10) > ,(1 ,'2016-01-28', 9) > ,(1 ,'2016-03-05', 12) > ,(1 ,'2016-05-12', 11) > ,(2 ,'2016-01-01', 12) > ,(2 ,'2016-02-02', 10) > ,(2 ,'2016-03-05', 12) > ,(2 ,'2016-04-07', 14) > ; > > SELECT * FROM table1; > > > -- ID | ISO8601_date | INT_value > -- --- | ------------ | --------- > -- 1 | 2016-01-01 | 10 > -- 1 | 2016-01-28 | 9 > -- 1 | 2016-03-05 | 12 > -- 1 | 2016-05-12 | 11 > -- 2 | 2016-01-01 | 12 > -- 2 | 2016-02-02 | 10 > -- 2 | 2016-03-05 | 12 > -- 2 | 2016-04-07 | 14 > > > -- This query just to show what's going on > > WITH C1(ID,DT,V) AS ( > SELECT ID, ISO8601_date, MAX(INT_value) FROM table1 GROUP BY > ID, ISO8601_date > ) > SELECT A.ID, A.DT, B.DT, C.DT, A.V, B.V, C.V > FROM C1 AS A > JOIN C1 AS B ON B.ID=A.ID AND B.DT = (SELECT MIN(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 MIN(X.DT) FROM C1 AS > X WHERE X.ID=A.ID AND X.DT>B.DT) > WHERE B.V > A.V AND C.V > B.V > ; > > -- ID | DT | DT | DT | V > | V | V > -- ------------ | ------------ | ------------ | ------------ | > ------------ | ------------ | ------------ > -- 2 | 2016-02-02 | 2016-03-05 | 2016-04-07 | > 10 | 12 | 14 > > > -- This is the actual result query showing just the ID's > > WITH C1(ID,DT,V) AS ( > SELECT ID, ISO8601_date, MAX(INT_value) FROM table1 GROUP BY > ID, ISO8601_date > ) > SELECT A.ID > FROM C1 AS A > JOIN C1 AS B ON B.ID=A.ID AND B.DT = (SELECT MIN(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 MIN(X.DT) FROM C1 AS > X WHERE X.ID=A.ID AND X.DT>B.DT) > WHERE B.V > A.V AND C.V > B.V > ; > > -- ID > -- ------------ > -- 2 > > > -- Script Stats: Total Script Execution Time: 0d 00h 00m > and 00.049s > -- Total Script Query Time: 0d 00h 00m > and 00.025s > -- Total Database Rows Changed: 8 > -- Total Virtual-Machine Steps: 4482 > -- Last executed Item Index: 5 > -- Last Script Error: > -- > ----------------------------------------------------------- > ------------------------------------- > > > > > _______________________________________________ > 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
