Unfortunately also the H2 SQL database implementation of a local database in KNIME is one version to old to support window functions:
The only way to use window functions in a local database with KNIME without installing MariaDB or Postgres or the like is using the Hive implementation in the local big data environment. But that is not suitable for permanent storage of data.
Of course the always is the rank node in KNIME itself:
the SQLite version that is shipped with KNIME Analytics Platform is 3.23.1. I have created a feature request to update the driver.
Until then you can also register a newer version of the driver and also the H2 driver. The SQLite JDBC drivers includes everything you need so simply download it from here and register it in KNIME as described in the DB documentation.