problem using group by

[toodi4]

I'm working with an auction program. Every time someone makes a bid, it enters a line in the auction_bid table. So I set up the following query to figure out each user's highest bid for a given auction id:

$sql = "select max(amount),username,auction_id from auction_bid GROUP BY auction_id, user_name";

when I run the above query, I get the desired result which is a list of the highest bid by each user in each auction.

Then, I want to run a query that just gets the winning bid for each auction_id. That seems simple, but for some reason the username isn't lining up with the bid. Here is the query:

$sql = "select max(amount),username, auction_id from auction_bid GROUP BY auction_id";

I do get the correct highest bid, but it isn't matching with the right user. What am I doing wrong?

[Lars_Berg]

select amount,
          username, 
          auction_id 
  from auction_bid as ab
 where amount =
          (select max(amount)
               from auction_bid
              where auction_bid.auction_id = ab.auction_id)