List:General Discussion« Previous MessageNext Message »
From:Mark Addison Date:March 21 2006 5:10pm
Subject:RE: mysql query and version problem .... Help!
View as plain text  
> -----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.

Thread
mysql query and version problem .... Help!Gregory Machin21 Mar
RE: mysql query and version problem .... Help!Mark Addison21 Mar
  • Re: mysql query and version problem .... Help!Gregory Machin22 Mar
    • Re: mysql query and version problem .... Help!Peter Brawley22 Mar
  • Re: mysql query and version problem .... Help!Gregory Machin22 Mar
    • Re: mysql query and version problem .... Help!Gabriel PREDA22 Mar