> -----Original Message-----
> From: Gregory Machin [mailto:gregory.machin@stripped]
> Sent: 21 March 2006 11:28
> To: mysql@stripped
> 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 postmaster@stripped
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.
