zimmer9
asked on
How to eliminate duplicate accounts selectively from a SQL Server 2005 table?
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.