> -----Original Message-----
> From: Gregory Machin [mailto:[EMAIL PROTECTED]
> Sent: 21 March 2006 11:28
> To: mysql@lists.mysql.com
> Subject: mysql query and version problem .... Help!
>
> Hi.
>
> I have just found out that my hosting provider is using mysql
> 4 and I'm
> using mysql 5 the one query I need wont work and is a key
> feature in the
> application .. here is the query i'm using
>
> 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';
>
> How do I get this to work on version 4 ?
You could create a tmp table with the max bids and then join on
that. Something like:
CREATE TEMPORARY TABLE max_bids
SELECT auto_id, MAX(bid_amount) AS max_bid_amount
FROM bids
GROUP BY auto_id;
SELECT dealer_id, b1.auto_id, bid_amount FROM bids b1, max_bids b2
WHERE b1.auto_id = b2.auto_id
AND bid_amount=max_bid_amount
AND auto_dealer_id = '3' AND Bid_Status = '1';
mark
--
Please Note:
Any views or opinions are solely those of the author and do not necessarily
represent
those of Independent Television News Limited unless specifically stated.
This email and any files attached are confidential and intended solely for the
use of the individual
or entity to which they are addressed.
If you have received this email in error, please notify [EMAIL PROTECTED]
Please note that to ensure regulatory compliance and for the protection of our
clients and business,
we may monitor and read messages sent to and from our systems.
Thank You.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
