Solved

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

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

789 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