Solved

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

Posted on 2011-03-10
3
264 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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Familiarize people with the process of utilizing SQL Server views 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 Access…
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…

705 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now