How to use SQL to find second-highest auction bids

I'm working on a small online auction site and I need some sql to determine what each item sold for. The bids table contains the following fields: bidID, itemID, bidderID, bidAmount, bidDate.

The site works basically like eBay, where if the item is currently at $10 and "bidder A" bids $50, the items price will remain $10 until a second bidder places a higher bid. Let's say "bidder B" places a $40 bid, then the item would be at ($40 + increment). The increment is, depending on the auction, either a fixed amount (say $5) or a percentage of the current price.

That's the overview. As for the sql, I think I need to find the highest and second-highest bids for each item a开发者_运维百科nd use those to determine the final price.

What's the best way to find each item's second-highest bid?

Also, just as a note, I'm stuck using SQL Server 2000, so the solution can't include ROW_NUMBER() or other more recent built-in functions.

Basically, you could do a TOP 2 and then wrap that in a SELECT statement and get only the one you want (the lower $ amount). Something like this:

SELECT TOP 1 *
FROM (
   SELECT TOP 2 *
   FROM table
   WHERE <criteria match>
   ORDER BY amount DESC
) AS newTable
ORDER BY amount ASC

I don't know if this is the most efficient solution but it may work:

SELECT TOP 2 * FROM bids WHERE itemID = ... ORDER BY bidAmount DESC