Gregory Machin wrote:
Ok I tried the following
SELECT dealer_id, auto_id, bid_amount FROM bids WHERE bid_amount=(SELECT
MAX(bid_amount) FROM bids WHERE auto_dealer_id='3');
which gives
+-----------+---------+------------+
| dealer_id | auto_id | bid_amount |
+-----------+---------+------------+
| 3 | 12 | 90000 |
+-----------+---------+------------+
1 row in set (0.00 sec)
wich is the max bid overall, what I want is the max bid for each auto_id ...
No, it's the max bid received by auto_dealer number 3. It's a coincidence if
that's also the max bid overall.
How would I go about this ?
By following the example in the link I sent.
SELECT dealer_id, auto_id, bid_amount
FROM bids b1
WHERE bid_amount=(SELECT MAX(b2.bid_amount)
FROM bids b2
WHERE b1.auto_id = b2.auto_id);
You see? Rows are selected if they have the max bid of all rows with the same
auto_id.
You keep saying you want the max bid per auto_id, but your examples always
include restrictions on auto_dealer_id. That's fine, but it's a separate issue.
You can just add any additional restrictions to the main query's WHERE clause:
SELECT dealer_id, auto_id, bid_amount
FROM bids b1
WHERE bid_amount=(SELECT MAX(b2.bid_amount)
FROM bids b2
WHERE b1.auto_id = b2.auto_id)
AND auto_dealer_id = '3' AND Bid_Status = '1';
Michael
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
