On Wed, Sep 19, 2012 at 7:10 PM, Mohd Radzi Ibrahim <imra...@gmail.com> wrote: > On Thu, Sep 20, 2012 at 12:58 AM, Igor Tandetnik <itandet...@mvps.org>wrote: > >> On 9/19/2012 12:51 PM, joe.fis...@tanguaylab.com wrote: >> >>> Too bad SQLite doesn't yet support SQL Window Functions. >>> >>> Are there any SQLite Extension Libraries that support "SQL:2003 type >>> Window Functions"? >>> I specifically need LEAD and LAG to calculate an event integer timestamp >>> delta between consecutive rows. >>> I've played with some self-join code but that's proving to be complicated. >>> >> >> The easiest approach is to maintain the window in your application code, >> as you iterate over a simple SELECT statement. >> -- >> Igor Tandetnik >> >> >> > 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. Pavel _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
