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
