Hi,

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:

Code:
"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.