It's working ok .. But I have one problem .. I'm getting the following error "Table 'max_bids3' already exists";
but I get the following ... mysql> DROP TABLE max_bids3; ERROR 1051 (42S02): Unknown table 'max_bids3' What do I do to fix this, and how can I see the temp tables ? On 3/21/06, Addison, Mark <[EMAIL PROTECTED]> wrote: > > > -----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. > > -- 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
