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
--------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
