Delete every row that is not the first duplicate

Question

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

Answer 1

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 "org_units" (
  "ID" INTEGER,
  "RESH_ID" VARCHAR(1)
);

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

  DELETE FROM "org_units" o 
where o."ID" in (SELECT MIN("ID")    FROM  "org_units" GROUP BY "RESH_ID" )

3 rows affected

SELECT * FROM org_units

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

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

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

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

3 rows affected

SELECT * FROM org_units2

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

db<>fiddle here