Hi. I have the following table | bid_id | dealer_id | auto_dealer_id | auto_id | bid_amount | timestamp | Bid_Status | +---------+-------------+----------------------+------------+-----------------+----------------+-----------------+ | 1 | 3 | 3 | 12 | 20000 | NULL | 1 | | 2 | 3 | 3 | 12 | 30000 | NULL | 1 | | 3 | 24 | 3 | 12 | 40000 | NULL | 1 | | 4 | 24 | 3 | 12 | 50000 | NULL | 1 | | 5 | 24 | 3 | 12 | 60000 | NULL | 1 | | 6 | 3 | 24 | 14 | 40000 | NULL | 1 | | 7 | 3 | 3 | 13 | 40000 | NULL | 1 | | 8 | 24 | 3 | 12 | 70000 | NULL | 1 | | 9 | 24 | 3 | 13 | 59000 | NULL | 1 | | 10 | 24 | 3 | 12 | 80000 | NULL | 1 | | 11 | 24 | 3 | 13 | 60000 | NULL | 1 |
where auto_dealer_id is the dealer who put the car on auction , auto_id is the id of the car on auction, bid_amount is the amount did on the car but dealer_id what I want is to get the max bid placed for each car and the dealer_id who placed it. I currnetly have the following: mysql> SELECT dealer_id, auto_id, bid_id ,MAX(bid_amount) AS 'bid_amount' FROM bids WHERE auto_dealer_id = '3' AND Bid_Status = '1' GROUP BY auto_id; +-----------+---------+--------+------------+ | dealer_id | auto_id | bid_id | bid_amount | +-----------+---------+--------+------------+ | 3 | 12 | 1 | 80000 | | 3 | 13 | 7 | 60000 | +-----------+---------+--------+------------+ But this output is wrong because if you refer to the soucrce table above the dealer_id should be 24 in both cases.. What have I missed ? Many Thanks Gregory Machin -- Gregory Machin [EMAIL PROTECTED] [EMAIL PROTECTED] www.linuxpro.co.za www.exponent.co.za Web Hosting Solutions Scalable Linux Solutions www.iberry.info (support and admin) +27 72 524 8096
