MySQL GROUP BY problem

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.

 

Can you post the relationships between the tables, or post the table structures?

 

You're almost certainly going to need to use the MAX function. But you need to let us see the table structures to get an answer that you can use.

 

In fact, putting around your bid value - MAX(bidvalue) - may be the way to go

 

I had this exact problem and I blogged about it, check out a simple solution here:
http://www.tjbourke.com/6