SQLite Forum

FILTER clause with window functions
They're described here:


Like the other "built-in" window functions, they don't support the FILTER clause. I think it's a standards thing. Postgres has the same restriction.

You could use the sqlite3_create_window_function() API to create versions of first_value() and last_value() that worked like regular aggregate window functions:


last_value() is quite trivial, and first_value() wouldn't be all that complicated either. A user implemented first_value() would use a little extra memory I suppose, but that might not be a problem in most cases.