List:General Discussion« Previous MessageNext Message »
From:Gregory Machin Date:March 17 2006 7:23am
Subject:problem with selecting my max bid ..
View as plain text  
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
Thread
problem with selecting my max bid ..Gregory Machin17 Mar
  • Re: problem with selecting my max bid ..Michael Stassen17 Mar
    • Re: problem with selecting my max bid ..Gregory Machin17 Mar
      • Re: problem with selecting my max bid ..Michael Stassen17 Mar
        • Re: problem with selecting my max bid ..Gregory Machin20 Mar