Delete duplicates based on rank using row id
DELETE
FROM magnet_dif_units
WHERE rowid IN (
SELECT "rowid"
FROM
(SELECT "rowid",
rank_n
FROM
(SELECT rank() over (partition BY dept order by rowid) rank_n,
rowid AS "rowid"
FROM magnet_dif_units
WHERE dept IN
(SELECT dept FROM magnet_dif_units GROUP BY dept HAVING COUNT(*) > 1
)
)
)
WHERE rank_n > 1
);