SitePoint Sponsor |
|
User Tag List
Results 1 to 11 of 11
Thread: MySQL GROUP BY problem
-
Jul 24, 2009, 06:43 #1
- Join Date
- Jul 2009
- Posts
- 52
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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"
Many thanks in advance.
-
Jul 24, 2009, 06:54 #2
max(bidvalue)
Guido - Community Team Leader
The Votes Are In: The Winners of the 2013 Community Awards are...
Blog - Free Flash Slideshow Widget
-
Jul 24, 2009, 06:58 #3
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jul 24, 2009, 07:00 #4
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
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...
-
Jul 24, 2009, 07:06 #5
- Join Date
- Jul 2009
- Posts
- 52
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Jul 24, 2009, 07:09 #6
- Join Date
- Jul 2009
- Posts
- 52
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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,Last edited by scim1971; Jul 24, 2009 at 07:10. Reason: forgot to mention about the ?
-
Jul 24, 2009, 07:13 #7
- Join Date
- Jul 2009
- Posts
- 52
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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
-
Jul 24, 2009, 07:34 #8
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
-
Jul 24, 2009, 07:56 #9
- Join Date
- Jul 2009
- Posts
- 52
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 24, 2009, 08:46 #10
- Join Date
- Jul 2002
- Location
- Toronto, Canada
- Posts
- 39,347
- Mentioned
- 63 Post(s)
- Tagged
- 3 Thread(s)
Code: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
-
Jul 24, 2009, 09:02 #11
- Join Date
- Jul 2009
- Posts
- 52
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Bookmarks