Solved

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

Posted on 2011-03-10
3
268 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:
ewangoya 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 38

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

Master Your Team's Linux and Cloud Stack

Come see why top tech companies like Mailchimp and Media Temple use Linux Academy to build their employee training programs.

Question has a verified solution.

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

Suggested Solutions

'Between' is such a common word we rarely think about it but in SQL it has a very specific definition we should be aware of. While most database vendors will have their own unique phrases to describe it (see references at end) the concept in common …
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

810 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