SQL Query - Eliminating Duplicates

[benchpolo]

I'm not sure if this is the right section to post, but somehow I cannot post a thread in SQL server section.

CreateDate AccountNo OriginalAcctNo
2009-05-08 10:53:24.380 200905086790003 6 200711169990083 8
2009-05-08 11:00:31.870 200905086790004 1 200711169990083 8

I need assistance in writing a SQL script that if the OriginalAcctNo is Duplicated like the sample above, to display the most recent record of create date.

Thanks in adnvance.

[improvcornartist]

Probably not the best way to do it, but if AccountNo is always incremented (most recently created accounts have highest account numbers), you might be able to select the max of each CreateDate and AccountNo while grouping on OriginalAcctNo:

SELECT max(CreateDate), max(AccountNo), OriginalAcctNo
FROM tbl
GROUP BY OriginalAcctNo

[NeoPa]

Unfortunately this is not quite so simple unless the later Account Numbers are guaranteed to be greater than all previous ones.

There are two techniques for this.
The more straightforward one uses a subquery (Subqueries in SQL) to determine the selected records in the groups, then links this record to the original record source.

Does this sound like sense to you?

Let us know how you get on or if you need more explanation.

[shawpnendu]

I think OriginalAcctNo is the key to filter. So you can try with below query:

SELECT MAX(AccountNo),OriginalAcctNo FROM tbl 
GROUP BY  OriginalAcctNo

Now check what the query return.
If successfull then delete in the following way:

DELETE FROM tbl where AccountNo NOT IN
(SELECT MAX(AccountNo) FROM tbl 
GROUP BY  OriginalAcctNo)

Regards