b1nary0mega
1/6/2015 - 7:03 PM

Delete duplicates based on rank using row id

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
);