DB SQL - H2 School of duplicates - and how to deal with them

DB SQL - H2 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 easienst ways to deal with them is 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 shoudl still be able to deal with them.

And you should be able to bring a messy dataset into a meaningful table with a nice unique ID without loosing too much information. And this workflow would like to encourage you to think about what to do with your duplicates and not to get caught off guard but to take control :-)

SELECT t2.*

/* mark the Top row per ID as the chosen one */
, CASE WHEN t2."rank_id" =1 THEN 'chosen'
ELSE 'duplicate'
END AS "duplicate_type_classifier"
FROM

/* start t2 */
( SELECT *

, ROW_NUMBER() OVER (
/* set which unique ID should be kept - all others will be deleted */
/* this makes sure even if there is a *complete* duplicate only one row will remain (and be intact) */
PARTITION BY t1."ID"
/* set rules which ID should remain on Top in this case the one with the latest enry and then the last contact */
ORDER BY t1."entry_date" DESC NULLS LAST
, t1."last_contact" DESC NULLS LAST
, t1."purchase" DESC NULLS LAST
) AS "rank_id"

FROM #table# AS t1
) t2
/* end t2 */

ORDER BY t2."ID"
, t2."rank_id"

https://forum.knime.com/t/remove-duplicate-and-get-correct-data-in-knime/84796/4?u=mlauber71

A pragmatic approach to multiple duplicates by takbb

https://forum.knime.com/t/duplicate-filter-with-more-conditions/90237/3?u=mlauber71