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