from sql bible there is a solution I will copy & paste it for you
Deleting duplicate rows using windowing
Of the three methods to remove duplicate rows, this method is the most straightforward because it
doesn't need to alter the table or generate a second table.
The key to this method is using the windowing’sOVER()clause with aROW_NUMBER()function and
a partition. The partition will begin renumbering with every new partition. Set theOVER()clause to
PARTITION BYevery column to be checked for duplicate data. In this case, every column is being
checked.
Running the windowing query first shows how it applies the row number:
SELECT Col1, Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2ORDER BY Col1) AS rn
FROM DupsNoPK
Result:
Col1 Col2 rn
----------- ----- --------------------
1 abc 1
2 abc 1
2 abc 2
2 abc 3
7 xyz 1
7 xyz 2
Every duplicate row has anrnvalue of greater than 1, so it’s now easy to delete the duplicates:
WITH DupsNumbered
AS (
SELECT Col1, Col2,
ROW_NUMBER() OVER (PARTITION BY Col1, Col2 ORDER BY Col1) AS rn
FROM DupsNoPK
)
DELETE DupsNumbered
WHERE rn > 1;
The nextSELECTtests the effect of the windowing remove duplicates query:
SELECT Col1, Col2
FROM DupsNoPK;
Result:
Col1 Col2
----------- -----
1 abc
2 abc
7 xyz