# SQLITE and Window Functions

**URL:** <https://forum.knime.com/t/sqlite-and-window-functions/31608>\
**Category:** KNIME Analytics Platform\
**Created:** [March 18, 2021, 10:57pm UTC](https://forum.knime.com/t/sqlite-and-window-functions/31608 "2021-03-18T22:57:01Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\
**Post date:** [March 19, 2021, 6:59am UTC](https://forum.knime.com/t/sqlite-and-window-functions/31608/2 "2021-03-19T06:59:50Z")

</div>

@khatuaani welcome to the KNIME forum.

To find the sqlite version [this command](https://stackoverflow.com/questions/9646353/how-to-find-sqlite-database-file-version) should help

```auto
select sqlite_version();

```

[https://www.sqlite.org/lang\_corefunc.html#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](http://www.h2database.com/html/functions.html#h2version) to old to support window functions:

> [@RANK variable in DB](https://forum.knime.com/t/rank-variable-in-db/30564/7):
>
> @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](https://www.h2database.com/html/functions-window.html#rank)). 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 slight_smile

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](https://hub.knime.com/mlauber71/spaces/Public/latest/kn_example_hive_school_of) 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](https://forum.knime.com/t/school-of-duplicates-and-how-to-deal-with-them/24164/2):
>
> 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](https://forum.knime.com/t/replace-simplify-row-values-to-eliminate-duplicates/24121/9) - 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](https://forum.knime.com/t/help-needed-with-multi-row-formula/26479/6):
>
> Hello @sauravh86, if I understood it correctly to get your position column you can use [Rank](https://kni.me/n/-nXP9ULsMFxDeH3m) node as you can define Group column there. Only prior to it you can add [Counter Generation](https://kni.me/n/pVT_aAQrO1Jkt9cG) node based on which you will do ranking. Br, Ivan

> **[Rank](https://hub.knime.com/knime/extensions/org.knime.features.base/latest/org.knime.base.node.preproc.rank.RankNodeFactory)**
>
> For each group an individual ranking is computed based on the selected ranking attribute(s) and ranking mode. The user must provide at least one attribute on w…

---

_[View the full topic](https://forum.knime.com/t/sqlite-and-window-functions/31608)._