deleting lines

[dz2k7]

Let's say we have a table:

Sipping branch Receiving Branch Product # Qty to ship
a c PR1 43
a d PR1 25
a k PR1 1
....


Now I want to delete all lines that have same product code and shipping branch as a privous line.
So the only max amounts for each product and each shipping branch will remain.

How to delete those lines?

Thank you.

 

[ByteMyzer]

You might try a DELETE query, something like this:

DELETE T1.*
FROM [b][i]MyTable[/i][/b] AS T1
WHERE T1.[b][i]Qty[/i][/b] <
 (SELECT MAX(T2.[b][i]Qty[/i][/b])
  FROM [b][i]MyTable[/i][/b] AS T2
  WHERE T2.[b][i]ShippingBranch[/i][/b] = T1.[b][i]ShippingBranch[/i][/b]
  AND T2.[b][i]Product[/i][/b] = T1.[b][i]Product[/i][/b]
 )
;

This will delete duplicate entries for the same Shipping Branch and Product where the entry's quantity is less than the maximum quantity for that Shipping Branch and Product.

See if this solution works for you.

 

[dz2k7]

Well it works but takes forever.
I have tables like 50000 lines.
it looks through all the table for each line, so it's slow.

Nad another problem is there are double lines remaining where shiping quantities are same. I mean if the are 2 pairs of branches with the same shipping brnach and same qtys to ship.

 

[ByteMyzer]

Are the ShippingBranch and Product fields indexed? If not, you will need to index these fields to speed up the process.

For the double lines remaining, is the Receiving Branch the same for both lines in each instance? If it is different, which one would you wish to keep? (NOTE: The rule you use to determine this must apply to all instances.) If it is the same, you will need another field to make the distinction between the two lines.

 

[dz2k7]

O, I see....

Thank you,

By the way...
How to delete any same lines in a table anyway?

 

[Moniker]

Check the "Find Duplicates" wizard when making a new query and it will walk you through it.