# School of duplicates - and how to deal with them

**URL:** <https://forum.knime.com/t/school-of-duplicates-and-how-to-deal-with-them/24164>\
**Category:** Knowledge sharing\
**Created:** [May 27, 2020, 5:04pm UTC](https://forum.knime.com/t/school-of-duplicates-and-how-to-deal-with-them/24164 "2020-05-27T17:04:25Z")\
**Posts on this page:** 1\
**Showing post:** 2

<div class="post-metadata">

**Author:** ![mlauber71](https://forum-cdn.knime.com/user_avatar/forum.knime.com/mlauber71/32/73590_2.png) [@mlauber71](https://forum.knime.com/u/mlauber71)\
**Post date:** [May 28, 2020, 9:14am UTC](https://forum.knime.com/t/school-of-duplicates-and-how-to-deal-with-them/24164/2 "2020-05-28T09:14:00Z")

</div>

As an addition you could use the FIRST\_VALUE and LAST\_VALUE function to get additional information in one step. In this demonstration the SQL server is Hive (KNIME Local Big Data) since neither H2 nor SQLite seemingly would provide such a feature.

@webstar initiated this [question](https://forum.knime.com/t/replace-simplify-row-values-to-eliminate-duplicates/24121/9) - thanks.

```
SELECT `t2`.* 

-- mark the Top row per ID ad the chosen one ...
, CASE WHEN `t2`.`rank_id` =1 THEN 'chosen'
		ELSE 'duplicate' 
		END AS `duplicate-type-classifier`
FROM 

-- start t2
( SELECT * 

-- also you could derive a FIRST_VALUE or LAST_VALUE within your group and add that to the table
, FIRST_VALUE(`t1`.`last_contact`) OVER (PARTITION BY `t1`.`ID` ORDER BY `t1`.`last_contact` DESC) as very_last_contact

, 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
		, `t1`.`last_contact` DESC
	) AS `rank_id`

FROM #table# AS `t1`
) `t2`
-- end t2

ORDER BY `t2`.`ID` 
		, `t2`.`rank_id`
```

---

_[View the full topic](https://forum.knime.com/t/school-of-duplicates-and-how-to-deal-with-them/24164)._