Issue with SELECT TOP 1

I am using C#, ASP.NET, SQL Ser开发者_StackOverflow中文版ver 2008 R2. My code is pretty simple, but I keep running into the same error.

I have a table Bids that has the columns OfferAmount and Status.

The Status is either "Active" or "Red". I want to calculate the average OfferAmount of the rows in the table that have Status = 'Active' and compare it to a preset value. If the calculated average OfferAmount is lower than the preset value, I then set the Status of the row with current Status = 'Active' AND with the lowest OfferAmount to 'Red'. I then re-run the process. I use a do/while loop with the condition that the calculated average > preset value.

Everything works perfectly, except for one condition. If I have two OfferAmounts that are equal AND I need to set both of their Status = 'Red' (so they are no longer part of my calculation for Status = 'Active').

In that case, my loop sets one OfferAmount to Status = 'Red', but it then skips the other OfferAmount. By skip, I mean it behaves as if it doesn't exist and moves on to the next highest OfferAmount. So I am left with a row that has its Status = 'Active'. It keeps going and marks the rest of the rows 'Red' above and below the value, but never comes back to that row.

This is the command I am using:

UPDATE Bids 
   SET Status = 'Red' 
 WHERE BidId IN (SELECT TOP 1 BidId 
                   FROM Bids 
                  WHERE Status = 'Active' 
                    AND ItemId = @ItemId 
               ORDER BY OfferAmount, BidDateAndTime DESC)

I tried a bunch of different sql commands that all worked except in this one case. I am beginning to think that it is a configuration issue.

You could do your comparison on the minimum bid amount, i.e.

UPDATE Bids SET 
  Status = 'Red' 
WHERE Status = 'Active' AND 
      ItemId = @ItemId AND  
      OfferAmount = (SELECT MIN(OfferAmount) 
                     FROM Bids 
                     WHERE Status = 'Active' AND 
                           ItemId = @ItemId

I haven't tried it but the idea should work.

If you add WITH TIES after TOP n you can get additional rows (resulting in more than n in total) that tie with the n-th row based on the values of the columns in the ORDER BY clause:

UPDATE Bids 
SET Status = 'Red' 
WHERE BidId in 
      (SELECT TOP 1 WITH TIES BidId 
       FROM Bids 
       WHERE Status = 'Active' 
         AND ItemId = @ItemId 
       ORDER BY OfferAmount)

thanks for the help. I didn't end up using it because before I saw your reply, I split it into two separate sql commands. 1st one retrieved the row ID using the same Select Top 1 Where statement I used before. The second command then set the Status of that row ID to 'Red'.

Either breaking it up fixed it or I wasn't putting it together as a single command correctly.

Thanks for the ideas!