SQLITE and Window Functions

[khatuaani]

Hi,

I am unable to use WINDOW functions while querying a sqlite database.

Is there a way to check what version of sqlite KNIME is using?

Is it possible to update the SQLITE version on my KNIME installation?

The latest version of sqlite do support window functions:
https://www.sqlite.org/windowfunctions.html

Any guidance is highly appreciated!!

Thanks!!

[mlauber71]

@khatuaani welcome to the KNIME forum.

To find the sqlite version this command should help

select sqlite_version();

https://www.sqlite.org/lang_corefunc.html#sqlite_version

Unfortunately also the H2 SQL database implementation of a local database in KNIME is one version to old to support window functions:

RANK variable in DB

@chanoufi_marwa one way to do it is to use a Hive environment and a RANK function. Other SQL databases should also support RANK like H2 (Window Functions). Hint: the KNIME implementation of H2 currently has the version 1.4.196. Unfortunately with H2 the RANK etc functions are only supported with version 1.4.198 or later … @ScottF might be an interesting case to update that version

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.

School of duplicates - and how to deal with them

As an addition you could use the FIRST_VALUE and LAST_VALUE function to get additional information in one step. In this demonstration the SQL server is Hive (KNIME Local Big Data) since neither H2 nor SQLite seemingly would provide such a feature. @webstar initiated this question - thanks. SELECT `t2`.* -- mark the Top row per ID ad the chosen one ... , CASE WHEN `t2`.`rank_id` =1 THEN 'chosen' ELSE 'duplicate' END AS `duplicate-type-classifier` FROM -- start t2 ( SELECT * -- al…

Of course the always is the rank node in KNIME itself:

Help needed with Multi row formula

Hello @sauravh86, if I understood it correctly to get your position column you can use Rank node as you can define Group column there. Only prior to it you can add Counter Generation node based on which you will do ranking. Br, Ivan

Rank

[tobias.koetter]

Hello khatuaani,
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.
Bye
Tobias

[mlauber71]

@tobias.koetter thank you for this hint this opens up the whole world of window functions. I downloaded the latest JAR file from the Maven site and told KNIME where to find the driver.

@khatuaani now you can benefit from the latest developments with H2 (and SQLite).

use H2 to produce a Position / Rank number within a group variable (window...