Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums  SQL Server 2005 Forums  Transact-SQL (2005)  Avoid using cursor to delete rows

Author  Topic  Wentu Starting Member 3 Posts Posted - 2007-04-27 : 13:04:30 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 and - Cost is never more than a certain coefficient @K more than the least costly row (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