> > Could it not be done with inner select of ROWID-n and ROWID+n to get the
> > LEAD and LAG row ?
> >
> > select
> > logtime as timeNow,
> > (select logtime from logtable where rowid=a.rowid-1) as timeBefore,
> > (select logtime from logtable where rowid=a.rowid+1) as timeAfter
> > from logtime;
>
> This will work only if the logtime table has consecutive rowids which
> is almost never the case.
create temporary table tlogtime as select logtime from logtime order by rowid;
select logtime as timeNow,
(select logtime from tlogtable where rowid=a.rowid-1) as timeBefore,
(select logtime from tlogtable where rowid=a.rowid+1) as timeAfter
from tlogtime as a;
drop temp.tlogtime;
That is, create a temporary table with the data correctly ordered you want in
sequentially numbered rowid's, then the correlated subqueries will work.
Performance will be entirely dependant on how many rows you are dealing with in
the temp table.
---
() ascii ribbon campaign against html e-mail
/\ www.asciiribbon.org
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
