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

Delete duplicates with certain criteria

P: n/a
If anyone could help, I would appreciate it.

I have a table as listed below.

I need to check for duplicates by the OrderItem field and if there are
duplicates, it then needs to keep the OrderItem with the highest
interval and discard all other duplicates for that OrderItem number.
If the OrderItem and the Interval are exactly the same, it just needs
to keep one entry for the OrderItem.

Every OrderItem will have an interval even if it is 0.

Here is an example of the table.

OrderItem Interval
2209123 0
2322641 0
2322642 0
2322891 0
2322641 0.000694444
2322891 0.000694444
2322642 0.003472222
2205711 0.016666667
2144506 0.036111111
2200634 0.0375
2322891 1.230555556
2322911 12.7625
2322911 12.7625
Below are the ones that should remain after it runs through the code.

OrderItem Interval
2322911 12.7625
2144506 0.036111111
2200634 0.0375
2205711 0.016666667
2209123 0
2322641 0.000694444
2322642 0.003472222
2322891 1.230555556
Any help would be greatly appreciated.

Thanks,
Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
Since there are exact dups (order and interval) the only clean way to
to perform a group and move the group'ed records to another table and
then copy them back. If you can add an ID key field you could then
use a delete SQL statement
Sm******@aol.com (Smythe32) wrote in message news:<7f**************************@posting.google. com>...
If anyone could help, I would appreciate it.

I have a table as listed below.

I need to check for duplicates by the OrderItem field and if there are
duplicates, it then needs to keep the OrderItem with the highest
interval and discard all other duplicates for that OrderItem number.
If the OrderItem and the Interval are exactly the same, it just needs
to keep one entry for the OrderItem.

Every OrderItem will have an interval even if it is 0.

Here is an example of the table.

OrderItem Interval
2209123 0
2322641 0
2322642 0
2322891 0
2322641 0.000694444
2322891 0.000694444
2322642 0.003472222
2205711 0.016666667
2144506 0.036111111
2200634 0.0375
2322891 1.230555556
2322911 12.7625
2322911 12.7625
Below are the ones that should remain after it runs through the code.

OrderItem Interval
2322911 12.7625
2144506 0.036111111
2200634 0.0375
2205711 0.016666667
2209123 0
2322641 0.000694444
2322642 0.003472222
2322891 1.230555556
Any help would be greatly appreciated.

Thanks,

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.