My SQL equivalent for Duplicate row filter , Concatenate, Pivot nodes

Hello everyone,
Though this might not be the best forum category to ask this.

I am trying to compile sql equivalent code for primary KNIME nodes (like group by, row filter so on)

Can someone help me with how to code Duplicate row filter, Concatenate, Pivot ?

Hi @berserkersap

This is indeed not really the right forum for that because it appears you are actually looking to move out of KNIME and put it directly in a query :sweat_smile:. The internet is your best friend in this case.

You are looking for operators like WHERE, DISTINCT, GROUP BY, CONCAT.
Some explanations on its usage in basic form:

The amount of resources on this is endless.

2 Likes

Hi @berserkersap , the operations/statements that @ArjenEX provided are correct, and on top of the w3schools resources that @ArjenEX suggested, you can also look for these on the mysql doc pages too.

1 Like

You could use window functions of your database does support them:

For H2 and SQLite the latest KNIME nodes should already support them.

3 Likes

Hi @mlauber71 , I think the request was specifically for Mysql :slight_smile:

2 Likes

Hello @ArjenEX , @bruno29a , @mlauber71
Thank you for your replies.
Actually I know I can get most of those on internet.
However, I am particularly worried about duplicate row filter, on how to retain order and get the top first row and remove others like how KNIME does.

If I just ignore the order, I think I can use row_number() over window function to get the duplicates.

I am not that much of a KNIME expert but by mlauber71 words, it seems we can use SQL nodes by particularly writing a query in them (Well right now I am not worried about databases). However, I want to know if these nodes use non java related engines ?

(Honestly I want to get accuracy in numbers but as double data type in regular nodes cannot provide it , I wanted to explore new options. However, if SQL nodes give accuracy that can make things simpler maybe :sweat_smile:)

Maybe you take a look at this article dealing with duplicates. The other link would give you an example how to use Window functions like rank to control what would count as a duplicate.

MySQL also does support these functions:

https://dev.mysql.com/doc/refman/8.0/en/window-function-descriptions.html

1 Like