Results 1 to 9 of 9
  1. #1
    Join Date
    Sep 2011
    Posts
    3

    Unanswered: Query to return top 3 bidders without dupes?

    I'll start by saying I am a complete rookie at this and have taught myself what little I know by searching the Internet.

    In short, I have an online auction where the top three bidders will "win."

    I need to be able to order the quesry results by top three bids without including more than one bid from the same bidder. I'm stuggling with making it work and selecting the max bids for each bidder.

    For example, the database has the following bids in it:
    Jack White 30
    John Smith 32
    Steve Jones 35
    Bob Wilson 40
    John Smith 45

    I need the results to be:
    John Smith 45
    Bob Wilson 40
    Steve Jones 35

    But I'm getting strange results like:
    John Smith 32
    Bob Wilson 40
    Steve Jones 35

    My query is:
    $select = mysql_query("SELECT First,Last,Bid FROM Bids GROUP BY First,Last ORDER BY Bid DESC LIMIT 3") or die(mysql_error());

    Any help to get this to work correctly? Thanks

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    why do you need a group by clause?
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    PHP Code:
    $select mysql_query("SELECT First,Last,Bid FROM Bids GROUP BY First,Last ORDER BY Max(Bid) DESC LIMIT 3") or die(mysql_error()); 
    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    pat, you know as well as i do that you cannot sort by an expression that isn't included in the SELECT clause

    surely you meant to have Max(Bid) in the SELECT clause, and not just Bid
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Do I get half credit ???

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Pat Phelan View Post
    Do I get half credit ???
    depends if your query actually produces the desired results

    consider this data

    tom 45
    tom 43
    tom 41
    dick 39
    dick 37
    harry 42
    harry 40
    todd 48
    todd 44

    your query would give

    tom 45
    dick 39
    harry 42
    todd 48

    however, the "top three bids without including more than one bid from the same bidder" would be

    todd 48
    tom 45
    harry 42
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Sep 2011
    Posts
    3
    So is it as easy as adding MAX up front in SELECT?

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I think that you should be Ok if you use
    PHP Code:
    $select mysql_query("SELECT First, Last, Max(Bid)
       FROM Bids
       GROUP BY First, Last
       ORDER BY Max(Bid) DESC
       LIMIT 3"
    ) or die(mysql_error()); 
    Unfortunately I don't have anything handy to test it with, so you'll have to try it and see if the results are what you want or not.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  9. #9
    Join Date
    Sep 2011
    Posts
    3
    That resulted in the first and last names populating the results table but the bid column was blank.

Posting Permissions

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