Icon

kn_​forum_​31608_​h2_​group_​rank_​window_​function

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

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

use custom JDBC driver - THX tobias.koetter for the hint


use H2 to produce a Position / Rank number within a group variable (window functions)use custom JDBC driver - THX tobias.koetter for the hinthttps://forum.knime.com/t/sqlite-and-window-functions/31608/4?u=mlauber71 SELECT "t2"."GroupID" , "t2"."Previous", "t2"."Next", "t2"."Id", "t2"."Position" FROM-- start t2( SELECT * , RANK() OVER (-- set which column should be the one within to calculate the rank / position PARTITION BY "t1"."GroupID"-- set rules which how tto sort the data within the group ID / partition-- NULLS could be counted LAST or FIRST ORDER BY "t1"."Previous" ASC NULLS FIRST , "t1"."Id" ASC NULLS FIRST , "t1"."Next" ASC NULLS FIRST ) AS "Position"FROM #table# AS "t1") "t2"-- end t2ORDER BY "t2"."Position" , "t2"."Id" , "t2"."Next" , "t2"."Previous" http://www.h2database.com/html/changelog.htmlhttps://stackoverflow.com/questions/32360807/row-number-over-with-order-by-in-h2As of V1.4.198, support for ROW_NUMBER (and some other window functions) was added(see H2 Changelog), so now your query should work as expected. https://www.stichlberger.com/software/get-h2-database-version-string/SELECT H2VERSION() FROM DUAL alternative: SQL Executor withDROP TABLE IF EXISTS default.data_all;the Query that produces thePosition grouped by IDresultcustomdriverdata_alldata_allh2_version1.4.1961.4.2002.1.212data_source.xlsx/data/data_result.xlsxdata_all DB Table Remover DB Query DB Reader H2 Connector DB Loader DB Table Selector DB Query Reader Excel Reader Excel Writer DB Table Creator use H2 to produce a Position / Rank number within a group variable (window functions)use custom JDBC driver - THX tobias.koetter for the hinthttps://forum.knime.com/t/sqlite-and-window-functions/31608/4?u=mlauber71 SELECT "t2"."GroupID" , "t2"."Previous", "t2"."Next", "t2"."Id", "t2"."Position" FROM-- start t2( SELECT * , RANK() OVER (-- set which column should be the one within to calculate the rank / position PARTITION BY "t1"."GroupID"-- set rules which how tto sort the data within the group ID / partition-- NULLS could be counted LAST or FIRST ORDER BY "t1"."Previous" ASC NULLS FIRST , "t1"."Id" ASC NULLS FIRST , "t1"."Next" ASC NULLS FIRST ) AS "Position"FROM #table# AS "t1") "t2"-- end t2ORDER BY "t2"."Position" , "t2"."Id" , "t2"."Next" , "t2"."Previous" http://www.h2database.com/html/changelog.htmlhttps://stackoverflow.com/questions/32360807/row-number-over-with-order-by-in-h2As of V1.4.198, support for ROW_NUMBER (and some other window functions) was added(see H2 Changelog), so now your query should work as expected. https://www.stichlberger.com/software/get-h2-database-version-string/SELECT H2VERSION() FROM DUAL alternative: SQL Executor withDROP TABLE IF EXISTS default.data_all;the Query that produces thePosition grouped by IDresultcustomdriverdata_alldata_allh2_version1.4.1961.4.2002.1.212data_source.xlsx/data/data_result.xlsxdata_allDB Table Remover DB Query DB Reader H2 Connector DB Loader DB Table Selector DB Query Reader Excel Reader Excel Writer DB Table Creator

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: m_lauber
Created at: 2020-09-03
On NodePit since: 2021-03-30
Last update: 2024-05-01
Created with KNIME version: v4.7.8
Tags: positionranknullsfirsth2sqlwindow functionwindowfunction

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!