SQLite Forum

FILTER clause with window functions
I'd like to add my support for RoboManni's request!

Whether this conforms to standards or not, I often find myself working with sparse data and wanting "the most recent non-null value from column X" (for tracking running subtotals, calculating differences to reference values etc.).

On oracle DB's I have used `last_value( col ) over ( partition ... order ... rows between ... ignore nulls )` to good effect - which at least feels like `filter ( where X is not null )` should be able to do the same.

Personally, I use SQLite as it is embedded in other tools - so I don't consider writing an extension a viable option.

Some time ago, Keith Medcalf replied to my query about this on the mailing lists with a solution along the lines of:

   ( select prev.col from tbl prev where prev.tick <= this.tick and prev.col is not null order by timestamp desc ) as last_value
from tbl this
where ...

Kudos to Keith for finding *a* solution, but my experience wth this technique has been "not great", it's slow and I find it quickly leads to unintelligible queries, especially when multiple columns are involved :-(

Cheers and thanks to all the SQLite dev's - an absolutely awesome tool!