Hi
i need help with this task i can't think how to do better.
I have a table with MyField1, MyField2, Cost, SomeOtherField
For each couple of MyField1, MyField2 i have several rows.
I need to delete some rows so that:
- for each couple of MyField1, MyField2 i have at most @MaxNumber rows remaining and
- for each pair MyField1,MyField2 the Cost in the remaining rows is never more than a certain coefficient @K times the cost of the least costly row of the pair (each MyField1,MyField2 has its own minimum cost).
I did this easily with a Cursor but i needed better performances.
I tried with this:
delete from MyTable from MyTable as p1 where
0 = (select (1 + sign(@MaxNumber -1-count(*))) * (1 + sign(min(p2.Cost) * @K- p1.Cost) )
from MyTable p2 where p2.MyField1 = p1.MyField1 and p2.MyField2 = p1.MyField2 and p2.Cost< p1.Cost)
What's happening here ? for each row i count how many rows are there with the same MyFields that have cost less than @K*MinValue , i multiply the two difference between the value i need and the real value and only when one of these values are zero i know that the product is zero and so i can choose the row to be deleted. Note that the double "FROM" in the query is no mistake.
This is terribly involuted, it works better than the cursor but i am sure there must be a better way to do this !!
Could You please help me ??
Thankx
Wentu