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
