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
greg@stripped
gregory.machin@stripped
www.linuxpro.co.za
www.exponent.co.za
Web Hosting Solutions
Scalable Linux Solutions
www.iberry.info (support and admin)
+27 72 524 8096
