I want to enforce uniqueness on a column, but in order to do so, I need to manually clean up a lot of duplicates. I have found out that all the duplicates have no references to them, so a simple sorting by resh id and then manually deleting every duplicate resh id apart from the first one with works (a la
DELETE FROM org_units WHERE id in (3,4,5)), but it is boring, so of course I would like to create some kind of query to clean up this mess.
The data looks like this:
org_units ID | RESH_ID ------------- 1 | a 2 | b 3 | a 4 | a 5 | b 6 | c
A first step would perhaps to do some kind of ordering:
select id, resh_id from org_unit order by resh_id, id;
I am not sure how to go from here to deleting everything apart from the first row in each "group" (group by might be handy?).
I would like to end up with a situation like
ID | RESH_ID ------------- 1 | a 2 | b 6 | c