SQLITE and Window Functions

[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