Thanks, will give that a try as well, but I am happy with the solution form R Smith. Only one relatively simple SQL and quite fast. Took about 1.5 secs to process 50000 rows.
RBS On Thu, Oct 20, 2016 at 4:55 PM, Jean-Luc Hainaut < jean-luc.hain...@unamur.be> wrote: > > What about this one? > > create table TABLE1(Seq integer primary key autoincrement, > Id integer, Date date, Value integer); > insert into TABLE1(Id,Date,Value) values(2,'2004-06-23', 42), (...), ...; > > select distinct T1.Id -- only one per Id > from TABLE1 T1, TABLE1 T2, TABLE1 T3 -- very fast join on > indexed rowid > where T2.Seq = T1.Seq+1 and T3.Seq = T1.Seq+2 -- consecutive triples > and T1.Id = T2.Id and T3.Id + T1.Id -- same Id > and T1.Value < T2.Value and T2.Value < T3.Value; -- ascending Values > > Works if: > - the rows are inserted in ascending values of (Id,Date) (if they don't, > just create and load a temp table with the sorted rows of TABLE1) > - and if the rows are unique on (Id,Date). > > If rowid column Seq cannot be added to source TABLE1, load data in temp > table TABLE2 with columns (Seq,Id,Date,Value). > > Probably not as fast as a purely procedural algorithm and less elegant > than previous proposals, > but probably faster than previous proposals and more elegant than a purely > procedural one! > > J-L Hainaut > > > _______________________________________________ > 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
