Icon

kn_​forum_​26479_​hive_​group_​rank2

use Hive to produce a Position / Rank number within a group variable

use Hive to produce a Position / Rank number within a group variable

use Hive to produce a Position / Rank number within a group variablehttps://forum.knime.com/t/help-needed-with-multi-row-formula/26479/5?u=mlauber71 SELECT `t2`.`Id` , `t2`.`PreviousId`, `t2`.`NextId`, `t2`.`TrackDimId`, `t2`.`Position` FROM-- start t2( SELECT * , ROW_NUMBER() OVER (-- set which column should be the one within to calculate the rank / position PARTITION BY `t1`.`TrackDimId`-- set rules which how tto sort the data within the group ID / partition-- NULLS could be counted LAST or FIRST ORDER BY `t1`.`PreviousId` ASC NULLS FIRST , `t1`.`NextId` ASC NULLS FIRST ) AS `Position`FROM #table# AS `t1`) `t2`-- end t2ORDER BY `t2`.`TrackDimId` , `t2`.`Position` DESC Position_test.xlsxdata_alldata_allalternative: SQL Executor withDROP TABLE IF EXISTS default.data_all;=> deletes the wholelocal big data folderdata/local_big_dataif you encouter any problems, closeKNIME and delete all data from the folder/data/local_big_data/the Query that produces thePosition grouped by IDresultknime://knime.workflow/data_result.xlsx Excel Reader (XLS) DB Table Creator DB Loader DB Table Remover local big datacontext create DB Query DB Reader Excel Writer (XLS) use Hive to produce a Position / Rank number within a group variablehttps://forum.knime.com/t/help-needed-with-multi-row-formula/26479/5?u=mlauber71 SELECT `t2`.`Id` , `t2`.`PreviousId`, `t2`.`NextId`, `t2`.`TrackDimId`, `t2`.`Position` FROM-- start t2( SELECT * , ROW_NUMBER() OVER (-- set which column should be the one within to calculate the rank / position PARTITION BY `t1`.`TrackDimId`-- set rules which how tto sort the data within the group ID / partition-- NULLS could be counted LAST or FIRST ORDER BY `t1`.`PreviousId` ASC NULLS FIRST , `t1`.`NextId` ASC NULLS FIRST ) AS `Position`FROM #table# AS `t1`) `t2`-- end t2ORDER BY `t2`.`TrackDimId` , `t2`.`Position` DESC Position_test.xlsxdata_alldata_allalternative: SQL Executor withDROP TABLE IF EXISTS default.data_all;=> deletes the wholelocal big data folderdata/local_big_dataif you encouter any problems, closeKNIME and delete all data from the folder/data/local_big_data/the Query that produces thePosition grouped by IDresultknime://knime.workflow/data_result.xlsxExcel Reader (XLS) DB Table Creator DB Loader DB Table Remover local big datacontext create DB Query DB Reader Excel Writer (XLS)

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: 2022-02-06
Last update: 2024-05-08
Created with KNIME version: v4.2.1
Tags: hivepositionranknullsfirst

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

Try NodePit Runner!