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)  Find nth row for specific result (very hard one)

Author  Topic  jacwak Starting Member 1 Post Posted - 2009-04-18 : 01:09:25 Hi, I have a special auction system database. The user doesn't pay for the product but for each offer. The rules are simple: 1. Each auction has max value, that a user can't make an offer larger than it. Each auction also has an end date. 2. If two users(or more) makes the same offer value, their offers got "burned" and are out of the game. 3. The winner is the one with the highest value at the auction end date. To help users to compute their offers, all the users can see a list of all the offers (but not the offer value). They can see 1) the rank of the offer(1, 2, 3...). This only valid for unburned offers. 2) the user id of the owner of the offer. 3) if the offer is burned or not. 4) Each user can see the offer value of his offers only. Other users offers values are hidden. So far so good. (And here starts the fun) There are 2 more complicated rules that applies only for the top 50 offers that are not burned: 1. If a user has ,within these 50 offers, 4 offers in sequent ranking, then the lowest offer of these 4 offers got burned. 2. If a user has, within these 50 offers, 10 offers (never mind their rank), then the lowest offer of these 10 offers got burned. Let's call these rules "Rules50" To make things more interesting: Rules50 check should run again and again until there is no more offers to burn. Because burning an offer, remove it from the top 50 offers, and Rule50 should run again on the new set of top 50!!!! So far I implemented rule50 using cursors and temp tables and it seems to run slowly. The question is: What is the best script to write to perform the Rule50 check?! Considering the offers are stored in sql server 2005 SP3, in a table name 'Offer' and has columns: UserID, Value, IsBurned, AuctionID Thank you very much! Jacob