0

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
1
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

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.