Icon

kn_​forum_​26479_​hive_​group_​rank

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`.`GroupID` , `t2`.`Previous`, `t2`.`Next`, `t2`.`Id`, `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`.`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` alternative: SQL Executor withDROP TABLE IF EXISTS default.data_all;load datainto hivetable" data_all" 1st cretae (empty)hive table" data_all" the Query that produces thePosition grouped by IDresult=> deletes the wholelocal big data folder/big_datadecide in the configuration if you want sub-folders or parent foldersif you encouter any problems, closeKNIME and delete all data from the folder/big_data/data_source.xlsxdata_result.xlsxDB Table Remover DB Loader DB Table Creator DB Query DB Reader local big datacontext create Excel Reader Excel Writer 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`.`GroupID` , `t2`.`Previous`, `t2`.`Next`, `t2`.`Id`, `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`.`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` alternative: SQL Executor withDROP TABLE IF EXISTS default.data_all;load datainto hivetable" data_all" 1st cretae (empty)hive table" data_all" the Query that produces thePosition grouped by IDresult=> deletes the wholelocal big data folder/big_datadecide in the configuration if you want sub-folders or parent foldersif you encouter any problems, closeKNIME and delete all data from the folder/big_data/data_source.xlsxdata_result.xlsxDB Table Remover DB Loader DB Table Creator DB Query DB Reader local big datacontext create Excel Reader Excel Writer

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: 2020-09-04
Last update: 2025-07-05
Created with KNIME version: v4.7.8
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!