Thanks for the further input, It works perfectly ... If you wouldn't mind please explain, the use of the b1 and b2 are they intended as varaibles ..
On 3/17/06, Michael Stassen <[EMAIL PROTECTED]> wrote: > > 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 > -- 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
