JOIN + GROUP BY question

[Csongor Fagyal]

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?

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