List Duplicates Between Two Files and Delete

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

List Duplicates Between Two Files and Delete

Author

Topic

darryldevin1
Starting Member

1 Post

Posted - 2012-12-29 : 09:12:49

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

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2012-12-29 : 10:21:44

[code]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[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources