Using SQL 2008 and imported two files into a table. The first file (2048) has 6,721 rows, the second file has (2209) 4,707 rows and the columns are: Billed, FirstName, LastName, FileID. Table is called 'Claims'.
Need query to list each FileId (2209 and 2048) showing the duplicates in each file and delete the duplicates from one of them.
Ran this query:
SELECT firstname , lastname , duplicatecount = COUNT(1 FROM Claims WHERE fileid IN (2209, 2048) GROUP BY firstname , lastname HAVING COUNT(1) > 1 ORDER BY COUNT(1) DESC
Delete T from
(
Select *,ROW_NUMBER() OVER (PARTITION BY FirstName,LastName Order by FileId) as Seq
from Claims
Where FileId in (2209, 2048)
)T
Where T.Seq > 1
List Duplicates Between Two Files and Delete
Posted - 12/29/2012 : 09:12:49
