SitePoint Sponsor

User Tag List

Results 1 to 11 of 11
  1. #1
    SitePoint Enthusiast
    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"
    Can any one help?

    Many thanks in advance.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    max(bidvalue)

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by scim1971 View Post
    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"
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 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...
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    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

  6. #6
    SitePoint Enthusiast
    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 ?

  7. #7
    SitePoint Enthusiast
    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
    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

  8. #8
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by scim1971 View Post
    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...

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  9. #9
    SitePoint Enthusiast
    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.

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Enthusiast
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •