Thanks for that, trying this out now. Must have done something wrong as for now I get too many ID's. My actual fields are somewhat different. It is indeed slow, something like 150 secs to process a table of 100000 rows. This is with select distinct ID etc. To do this in code will be indeed a lot faster and easier as well, but want to give SQL a try.
RBS On Wed, Oct 19, 2016 at 4:34 PM, Hick Gunter <h...@scigames.at> wrote: > Write a procedural program... ;) > > In SQL it requires a triple self join similar to: > > Select t1.id from table1 t1, table1 t2, table1 t3 where t2.id=t1.d and > t1.value < t2.value and t2.date = (select min(date) from table1 where id = > t.id and date > t1.date) and t3.id=t2.id and t2.value < t3.value and > t3.date = (select min(date) from t3 where id=t2.id and date > t2.date); > > -----Ursprüngliche Nachricht----- > Von: sqlite-users [mailto:sqlite-users-boun...@mailinglists.sqlite.org] > Im Auftrag von Bart Smissaert > Gesendet: Mittwoch, 19. Oktober 2016 16:53 > An: General Discussion of SQLite Database <sqlite-users@mailinglists. > sqlite.org> > Betreff: [sqlite] 2 consecutive rises in value > > 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? > > RBS > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > > > ___________________________________________ > Gunter Hick > Software Engineer > Scientific Games International GmbH > FN 157284 a, HG Wien > Klitschgasse 2-4, A-1130 Vienna, Austria > Tel: +43 1 80100 0 > E-Mail: h...@scigames.at > > This communication (including any attachments) is intended for the use of > the intended recipient(s) only and may contain information that is > confidential, privileged or legally protected. Any unauthorized use or > dissemination of this communication is strictly prohibited. If you have > received this communication in error, please immediately notify the sender > by return e-mail message and delete all copies of the original > communication. Thank you for your cooperation. > > > _______________________________________________ > 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
