By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,856 Members | 2,179 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,856 IT Pros & Developers. It's quick & easy.

SQL Query - Eliminating Duplicates

benchpolo
100+
P: 142
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 2009050867900036 2007111699900838
2009-05-08 11:00:31.870 2009050867900041 2007111699900838

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.
May 29 '09 #1
Share this Question
Share on Google+
3 Replies


Expert 100+
P: 303
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT max(CreateDate), max(AccountNo), OriginalAcctNo
  2. FROM tbl
  3. GROUP BY OriginalAcctNo
  4.  
May 29 '09 #2

NeoPa
Expert Mod 15k+
P: 31,541
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.
May 30 '09 #3

shawpnendu
P: 3
I think OriginalAcctNo is the key to filter. So you can try with below query:
Expand|Select|Wrap|Line Numbers
  1. SELECT MAX(AccountNo),OriginalAcctNo FROM tbl 
  2. GROUP BY  OriginalAcctNo
Now check what the query return.
If successfull then delete in the following way:
Expand|Select|Wrap|Line Numbers
  1. DELETE FROM tbl where AccountNo NOT IN
  2. (SELECT MAX(AccountNo) FROM tbl 
  3. GROUP BY  OriginalAcctNo)
Regards
Jun 2 '09 #4

Post your reply

Sign in to post your reply or Sign up for a free account.