At 20:21 +0100 12/17/02, Csongor Fagyal wrote:
>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.