Postgres 12

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE thetable (id INTEGER, role INTEGER, unit INTEGER);

INSERT INTO thetable (id, role, unit)
VALUES
  ('946', '1001', '1'),
  ('946', '1002', '1'),
  ('946', '1003', '1'),
  ('946', '1001', '2'),
  ('946', '1002', '2'),
  ('900', '1001', '3'),
  ('900', '1002', '3'),
  ('900', '1001', '3');
8 rows affected

DELETE FROM thetable t
WHERE t.unit IN (1,2)
AND EXISTS (
  SELECT 1 FROM thetable
  WHERE (id, role) = (t.id, t.role) AND unit < t.unit
)
2 rows affected

SELECT * FROM thetable


id
role
unit


946
1001
1

946
1002
1

946
1003
1

900
1001
3

900
1002
3

900
1001
3