MySQL GROUP BY problem

[scim1971]

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:

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

[guido2004]

max(bidvalue)

[r937]

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"

i'd love to help you but you have obfuscated your columns

i can't tell from the query which table each column belongs to

to say nothing of gleaning the PK/FK relationships...

[r937]

by the way this is an interesting problem, because finding the user's latest bid requires one subquery, and finding the highest bid requires another

GROUP BY on the outer query is not going to cut it...

[scim1971]

Hi,

thanks guido2004 - I did try Max(bidvalue) but because, in my list of results, I need the unique ID ("bidId") of each bid (for a bit of status logic that occurs on the page) this didn't work as the "bidId" didn't correspond to the bidvalue being shown (i.e. it was the bidId for a different bidvalue).

thanks anyway

[scim1971]

Sorry r937,

I was trying to keep it simple for illustration purposes - the table/column relationships are:

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

also tblbids.bidcustId = ? just refers to a cookie containing the members ID

many thanks,

[scim1971]

Hi r937,

by the way this is an interesting problem, because finding the user's latest bid requires one subquery, and finding the highest bid requires another

I think in this case we can assume the that users latest bid is also their highest bid - I'll put some validation logic in place when they place a bid that will only allow a bid to be placed if it's higher than their previous bid

[r937]

I think in this case we can assume the that users latest bid is also their highest bid

well, yeah

i thought you wanted to show the user if his latest bid just happened to be the highest bid for the lot -- you know, in case if it isn't, then he'll know that he has to place another bid if he wants that lot

but if you don't want this...

[scim1971]

I do but I'm doing that with ASP scripting further down the page - in the SQL statement I just need to get a list of their latest bid for each "lot" they are bidding on

thanks again.

[r937]

SELECT B.bidId
     , B.bidlotId
     , B.bidvalue
     , B.biddatetime
     , S.saleId
     , S.salename
     , S.saleclosingdate
     , L.lotnum
     , L.lotreserve
  FROM ( SELECT bidlotId
              , MAX(bidvalue) AS maxvalue
           FROM tblbids
          WHERE bidcustId = ?
         GROUP
             BY bidlotId ) AS M
INNER
  JOIN tblbids AS B
    ON B.bidlotId = M.bidlotId
   AND B.bidvalue = M.maxvalue
INNER
  JOIN tbllots AS L
    ON L.lotId = B.bidlotId 
INNER
  JOIN tblsales AS S
    ON S.saleId = B.bidsaleId 
   AND S.saleclosingdate > Now()
ORDER 
    BY S.saleclosingdate ASC
     , B.bidvalue DESC

[scim1971]

brilliant, fantastic, thank you so much.

Even if I live to a hundred I would have never come close to working that out.

You're a genius.

Thanks again.