Solved

How to eliminate duplicate accounts selectively from a SQL Server 2005 table?

Posted on 2011-03-10
3
274 Views
Last Modified: 2012-06-21
I am developing an Access application usng Access 2003,

I use an ADP type file with SQL Server 2005.

I have a table named tblGetDocumentAttributes with fileds including the following:

      MailID   Account   DocumentDate                     ScanDate    Amount.
 1   A34R   45677      2010-08-31 00:00:00.000
 2   B78E    45677      2010-09-30 00:00:00.000
 3   R546    45677      2011-01-31 00:00:00.000
 4   G555    45677      2011-01-31 00:00:00.000
 5   C645    86453      2010-08-31 00:00:00.000
 6   E567    86453      2010-09-30 00:00:00.000
 7   R546    86543      2011-01-31 00:00:00.000
 8   R555    86543      2011-02-28 00:00:00.000


Can you think of a SQL statement to delete any records from this table in which
any particular account has multiple records with the same documentdate and in these instances just keep the record with the higher MailID (type nvarchar) ?

So in the example above, the 4th record would be deleted because it has the same
account plus documentdate as record 3 but record 4 has the lower ranking MailID.
0
Comment
Question by:zimmer9
3 Comments
 
LVL 32

Accepted Solution

by:
Ephraim Wangoya earned 200 total points
ID: 35102543

delete
from tblGetDocumentAttributes A
where exists (select 1 from tblGetDocumentAttributes B
                      where B.Account = A.Account
                      and B.DocumentDate = A.DocumentDate                    
                      and B.MailID < A.MailID)
0
 
LVL 39

Assisted Solution

by:Aaron Tomosky
Aaron Tomosky earned 150 total points
ID: 35102597
In SQL you can do something like this:
While(exists(select count(*), documentdate from tblGetDocumentAttributes group by documentdate having count(*)>1))
begin
Delete from tblGetDocumentAttributes whare mailid = (select top 1 mailid from tblGetDocumentAttributes group by documentdate having count(*)>1 order by mailid);
End
0
 
LVL 11

Assisted Solution

by:JoeNuvo
JoeNuvo earned 150 total points
ID: 35102614
with SQL 2005
this command should do

;WITH CTE AS (
	SELECT MailID, ROW_NUMBER() OVER (PARTITION BY Account, DocumentDate ORDER BY MailID DESC) RN
	FROM tblGetDocumentAttributes
)
DELETE FROM tblGetDocumentAttributes
WHERE MailID IN
(
	SELECT MailID FROM CTE WHERE RN > 1
)

Open in new window

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

713 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question