In SQL, how can I remove duplicate rows based on multiple columns?

Question

I know that I can run the following query below to find "duplicate" rows based on multiple columns that do something like this:

SELECT      PosId, OrgId
FROM        PosOrg
GROUP BY    PosId, OrgId
HAVING COUNT(*) > 1

but now I want to remove duplicate rows so that the above query ends with returning zero rows. I don’t care which of the rows I delete (as many as only one row will remain based on the uniqueness of these two columns.

What is the correct way to remove these duplicates in SQL?

Answer 1

If you have another unique column id, you can do

delete from PosOrg
where id not in
(
  SELECT      min(id)
  FROM        PosOrg
  GROUP BY    PosId, OrgId
)

Answer 2

;WITH CTE
AS (
    SELECT PosId
          ,OrgId
          ,ROW_NUMBER() OVER (PARTITION BY PosId , OrgId ORDER BY PosId , OrgId) rn
    FROM   PosOrg
   )
DELETE FROM CTE
WHERE rn > 1