Search code examples
sqlitewindow-functions

Unknown error when using lag function with SQLITE

I have the following table. When I am missing a value from the num dimension, I would like to replace it with the value of the previous row. The following is an example of what I have tried. I'm using the lag window function, and is not working.

CREATE TABLE test(dt text, num INT);
INSERT INTO test VALUES("2011-11-11", 3);
INSERT INTO test VALUES("2011-11-12", NULL);
INSERT INTO test VALUES("2011-11-13", 5);
INSERT INTO test VALUES("2011-11-14", NULL);
INSERT INTO test VALUES("2011-11-15", NULL);
INSERT INTO test VALUES("2011-11-16", 2);

select dt,
       case when num is not null then num 
       else lag(num,1,0) over (order by dt) end 
from test

The error that I'm getting: OperationalError: near "(": syntax error which I am not even sure what it means. Any help will be appreciated.

Solution

  • Window function support was first added to SQLite with release version 3.25.0 (2018-09-15). You need to upgrade your SQLite binaries, or not use Window functions. See https://www.sqlite.org/windowfunctions.html#history