List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:December 19 2002 6:49pm
Subject:Re: JOIN + GROUP BY question
View as plain text  
At 20:21 +0100 12/17/02, Csongor Fagyal wrote:
>Hi,
>
>I have two tables: one holding bids for an auction (table bids) and 
>one holding user data who placed the bids (users). I would like to 
>get the highest bid, the user who placed the bid and the number of 
>bids placed, so I use the following query:
>
>
>SELECT MAX(bids.amount) AS amount, COUNT(bids.itemid) AS bidcount, 
>bids.bidderid, users.username FROM bids LEFT JOIN users ON 
>bids.bidderid=users.userid WHERE bids.itemid = 71580 AND 
>users.userid IS NOT NULL GROUP BY bids.itemid;
>
>What I get is not totally OK: amount is OK, COUNT is also OK, but 
>the user (users.username) I get is not the one who placed the 
>highest bid, but someone in the middle. How come? Am I 
>misunderstanding something about GROUP BY on a joined table?

No, you're misunderstanding something about GROUP BY.

When you group on a column or set of columns, you can select for output
those columns, and aggregate (summary) function values on other columns,
but you cannot select other columns.

You're grouping by itemid, but selecting bidderid and username for output.
So you get indeterminate results.

>
>THX,
>- Csongor

Thread
RE: SQL QuestionDarren Young16 Dec
  • Re: SQL QuestioniConnect \(Berlin\) Stefan Hinz16 Dec
    • Re: SQL QuestionKeith C. Ivey17 Dec
      • JOIN + GROUP BY questionCsongor Fagyal17 Dec
        • Re: JOIN + GROUP BY questionPaul DuBois19 Dec
      • Re: JOIN + GROUP BY questionCsongor Fagyal18 Dec