MySQL Query: Winning Auction Bid

I have a small Bidding system that I'm using for a fantasy auction draft. I'm trying to use the below query to pull up the max bids on each player. However, it's not actually giving me the max bid, it's just giving me the first one entered in to the database.

SELECT Bid.id FROM bids AS Bid 
WHERE Bid.active =1
GROUP BY player_id HAVING MAX( Bid.amount )

Here's the Bid table layout, in case it helps:

CREATE TABLE IF NOT EXISTS `bids` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `user_id` int(10) NOT NULL,
  `player_id` int(10) NOT NULL,
  `amount` int(6) NOT NULL,
  `timestamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `winning_bid` int(1) NOT NULL DEFAULT '0',
  `active` int(1) NOT NULL DEFAULT '1',
  PR开发者_开发百科IMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

---

select max(amount), player_id from bids 
 where active = 1
 group by player_id
 order by 1 desc;

---

Ever clause needs to do a boolean check. So, your having MAX(Bid.Amount) isn't do what you think it's doing. What you (theoretically) want is having Bid.Amount = MAX(Bid.Amount), although that leads to more issues since you should technically then group on Bid.Amount since it exists outside of an aggregate, and that's not what you mean to do at all.

(warning, queries below untested, but they should be quite close)

If you just want the max bid for a single player, what you should actually do is very simple, select the first record when ordering by bids in descending order:

select
  b.id
from
  bids b
where
  b.active = 1
  and b.player_id = @playerID
order by
  b.bids desc
limit 1

If you want it for all players, this query still gives you the starting point, you just need to pass in each individual player_id to it:

select
  bid.playerID,
  bid.id
from
  bids bid
  join (
    select
      b.id
    from
      bids b
    where
      b.active = 1
    order by
      b.bids desc
    limit 1
  ) maxbid on maxbid.playerid = bid.playerid

---

You could try:

SELECT Bid.id
  FROM bids AS Bid,
       (SELECT player_id, MAX(amount) AS player_max
          FROM bids
          WHERE active=1
          GROUP BY player_id) AS max_amounts
  WHERE Bid.player_id = max_amounts.player_id
    AND Bid.amount = max_amounts.amount;

I'm unsure of the behavior in case of ties, though.

---

Or, an alternative on Khorkrak's (which ofcourse works too):

SELECT Bid.id FROM bids AS Bid 
LEFT JOIN bids as x
ON x.amount > Bid.amount
AND x.active = 1
AND x.player_id = Bid.player_id
WHERE Bid.active = 1 AND x.player_id IS NULL
GROUP BY player_id

---

Here's one using a row rank to find the highest bid. You could also use something like this to find the 2nd highest, 3rd highest, etc...

SELECT id, player_id, amount 
FROM 
( 
select b.id, b.player_id, b.amount, count(*) as num 
from bids b left outer join bids b2 
on b.player_id = b2.player_id 
AND b.amount = b2.timestamp 
AND b.active = 1 
group by b.id, b.player_id, b.amount 
)
r 
WHERE
num = 1