I'm using MySQL with ASP pages. I'm building an auction style site and I want to show a member a list of their active bids. Because then can bid more than once for an "lot" I only want to show their highest bid for each "lot" - so, for instance if the bid (for one LOT) £50, then £75.00, then £100.00 it would only show the £100.00 bid.

The member can bid on many different "lots" so the list I want will show the "sale name", the sale "closing date", the "LOT number", their "bid", their "bid date" and a status message about whether they're the highest bidder or not.

My problem is that I can easily build the list to show "ALL" their bids and sort/order that list but it will include all the bods they've made for a particular "LOT" not just their highest bid.

I tried using DISTINCT on the bidlotId (the unique identifier for each lot in the bids table) but that didn't make any difference.

I then tried using GROUP BY bidlotId which does only show one bid for each lot but unfornately it's not the highest bid.

I need to figure out how to order/sort the GROUP BY function to show the highest bid for each lot.

This is my SQL:

"SELECT bidId, bidlotId, bidvalue, biddatetime, saleId, salename, saleclosingdate, lotnum, lotreserve
FROM dbname.tblbids, dbname.tblsales, dbname.tbllots
WHERE bidcustId = ? AND bidsaleId = saleId AND saleclosingdate > Now() AND bidlotId = lotId
GROUP BY bidlotId
ORDER BY saleclosingdate ASC, bidvalue DESC"

Can any one help?

Many thanks in advance.