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

[berserkersap]

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 ?

[ArjenEX]

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 . 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:

SQL WHERE Clause

SQL SELECT DISTINCT Statement

SQL GROUP BY Statement

MySQL CONCAT() Function

The amount of resources on this is endless.

[bruno29a]

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.

[mlauber71]

@berserkersap

code Duplicate row filter

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

SQLITE and Window Functions

@tobias.koetter thank you for this hint this opens up the whole world of window functions. I downloaded the latest JAR file from the Maven site and told KNIME where to find the driver. [image] [image] @khatuaani now you can benefit from the latest developments with H2 (and SQLite).

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

[bruno29a]

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

[berserkersap]

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 )

[mlauber71]

@berserkersap

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.

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.

School of duplicates - and how to deal with them

Dealing with duplicates is a constant theme with data scientist. And a lot of things can go wrong. The easiest ways to deal with them is SQL’s GROUP BY or DISTINCT. Just get rid of them and be done. But as this examples might demonstrate this might not always be the best option. Even if your data provider swears your combined IDs are unique especially in Big Data scenarios there might still be lurking some muddy duplicates and you should still be able to deal with them. And you should be able t…

MySQL also does support these functions:

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