> 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
I had a look at this and tried it without using the extra auto-increment field, using the table ROWID instead to check for consecutiveness. It would work great and a lot faster indeed if it wasn't for the multiple values on the same date. Problem with that is that if you group by date it may skip a ROWID, so it won't pick up that triple. The answer is just to clean that table up and clear these extra values on the same day. This will always need to be done in any case, so it might as well be done as a one off rather than in every select SQL. Thanks for that idea, it looks to me the fastest. 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
