SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 List Duplicates Between Two Files and Delete
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

darryldevin1
Starting Member

1 Posts

Posted - 12/29/2012 :  09:12:49  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

USA
7174 Posts

Posted - 12/29/2012 :  10:21:44  Show Profile  Reply with Quote
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
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.04 seconds. Powered By: Snitz Forums 2000