Delete every row that is not the first duplicate 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 &quot;group&quot; (group by might be handy?).
I would like to end up with a situation like
ID  | RESH_ID
-------------
1   |  a
2   |  b
6   |  c
This removes all rows apart from the first in each group ordered by resh id:
DELETE FROM org_unit o 
WHERE o.id NOT IN 
  (SELECT MIN(ID) AS id 
   FROM  org_unit 
   GROUP BY resh_id  
   ORDER BY resh_id);

If you wanted the last two or three, you would use the window function ROW_NUMBER and select the wanted number


CREATE TABLE &quot;org_units&quot; (
  &quot;ID&quot; INTEGER,
  &quot;RESH_ID&quot; VARCHAR(1)
);

INSERT INTO &quot;org_units&quot;
  (&quot;ID&quot;, &quot;RESH_ID&quot;)
VALUES
  ('1', 'a'),
  ('2', 'b'),
  ('3', 'a'),
  ('4', 'a'),
  ('5', 'b'),
  ('6', 'c');
  




  DELETE FROM &quot;org_units&quot; o 
where o.&quot;ID&quot; in (SELECT MIN(&quot;ID&quot;)    FROM  &quot;org_units&quot; GROUP BY &quot;RESH_ID&quot; )


3 rows affected




SELECT * FROM org_units


ID | RESH_ID
-: | :------
 3 | a      
 4 | a      
 5 | b      




CREATE TABLE org_units2 (
  &quot;ID&quot; INTEGER,
  &quot;RESH_ID&quot; VARCHAR(1)
);

INSERT INTO org_units2
  (&quot;ID&quot;, &quot;RESH_ID&quot;)
VALUES
  ('1', 'a'),
  ('2', 'b'),
  ('3', 'a'),
  ('4', 'a'),
  ('5', 'b'),
  ('6', 'c');




DELETE FROM &quot;org_units2&quot; o 
where o.&quot;ID&quot; NOT in (SELECT MIN(&quot;ID&quot;)    FROM  &quot;org_units2&quot; GROUP BY &quot;RESH_ID&quot; )


3 rows affected




SELECT * FROM org_units2


ID | RESH_ID
-: | :------
 1 | a      
 2 | b      
 6 | c      


db&lt;&gt;fiddle here