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