The row_number() method is by far the quickest and cleanest method. If you've never used row_number() before, do yourself a favor and learn it.
one modification to JP's code.. "... where RowNumber > 1" will delete all duplicates not just in cases where you only have 1 dup.
JP de Jong-202059 (11/30/2009)
Hi I prefer this syntax:WITH ItemsToBeDeleted
AS
(
SELECT *
, row_number() over (partition by item_no ORDER BY id) as RowNumber
FROM item_store
)
DELETE FROM ItemsToBeDeleted Where RowNumber = 2
Much more efficient.
Regards,
JP
Regards, Jim C