Message

From: Peter Brawley Date: April 25 2005 8:41pm Subject: Re: Query question List-Archive: http://lists.mysql.com/mysql/183104 Message-Id: <426D5604.8080207@earthlink.net> MIME-Version: 1.0 Content-Type: multipart/mixed; boundary="=======AVGMAIL-426D560463F6=======" --=======AVGMAIL-426D560463F6======= Content-Type: multipart/alternative; boundary=------------030702010509000704010800 --------------030702010509000704010800 Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 7bit Mathias, >Im sorry to disappoint you but this is an anti-performance solution. >Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table). This 2-query solution uses neither a join nor a subquery. What do you mean? PB ----- mathias fatene wrote: > Hi, > Im sorry to disappoint you but this is an anti-performance solution. > Use joins rathers than subqueries, and don't use joins if you can (all > data in the mother table). > > Imagine that table2 has 30.000.000 records, and not good indexes. you > can wait for your answer a long time. > > Best Regards > -------------------- > Mathias FATENE > > Hope that helps > /*This not an official mysql support answer/ > > > -----Original Message----- > *From:* Peter Brawley [mailto:peter.brawley@stripped] > *Sent:* lundi 25 avril 2005 22:17 > *To:* Jeff McKeon > *Cc:* mysql@stripped > *Subject:* Re: Query question > > Jeff, > > Then do it with 2 queries, > >SELECT @d := MAX( datestamp ) >FROM table2 >WHERE parentID = X; > > SELECT * > FROM table2 > WHERE parentID = X AND datestamp=@d; > > PB > > ----- > > Jeff McKeon wrote: > >>Thanks all but I don't have a mysql version high enough for subqueries. >> >>Thanks, >> >>Jeff >> >> >> >>>-----Original Message----- >>>From: Peter Brawley [mailto:peter.brawley@stripped] >>>Sent: Monday, April 25, 2005 4:01 PM >>>To: Jeff McKeon >>>Cc: mysql@stripped >>>Subject: Re: Query question >>> >>> >>>Jeff, >>> >>>Something like ... >>> >>>SELECT * >>>FROM table2 AS a >>>WHERE datestamp = ( >>> SELECT MAX( b.datestamp ) >>> FROM table2 AS b >>> WHERE a.parentID = b.parentID >>>); >>> >>>PB >>> >>>----- >>> >>> >>>Jeff McKeon wrote: >>> >>> >>> >>>>I have a table that contains records that link back to a >>>> >>>> >>>main talbe in >>> >>> >>>>a many to one configuration linked by table1.id = table2.parentid >>>> >>>>Table1 (one) >>>>Table2 (many) >>>> >>>>I want to pull the latest records from table2 for each >>>> >>>> >>>record in table1 >>> >>> >>>>where certain criteria applie. >>>> >>>>So, if record 100 in table1 links to 5 corresponding records >>>> >>>> >>>in table2, >>> >>> >>>>I want to pull the latest record from table2 where table2.parentid = >>>>100 and table2.user not like 'john' >>>> >>>>There is a datestamp field in table2. >>>> >>>>I just can't figure out how to do this. >>>> >>>>Thanks, >>>> >>>>Jeff >>>> >>>> >>>> >>>> >>>> >>>> >>>-- >>>No virus found in this outgoing message. >>>Checked by AVG Anti-Virus. >>>Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 >>> >>> >>> >>> >> >> >> >> >------------------------------------------------------------------------ > >No virus found in this incoming message. >Checked by AVG Anti-Virus. >Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 > > --------------030702010509000704010800 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Mathias,

>Im sorry to disappoint you but this is an anti-performance solution.

>Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table).

This 2-query solution uses neither a join nor a subquery. What do you mean?

PB

-----

mathias fatene wrote:

Message

Hi,

Im sorry to disappoint you but this is an anti-performance solution.

Use joins rathers than subqueries, and don't use joins if you can (all data in the mother table).

Imagine that table2 has 30.000.000 records, and not good indexes. you can wait for your answer a long time.

Best Regards

--------------------

Mathias FATENE

Hope that helps

*This not an official mysql support answer

-----Original Message-----
From: Peter Brawley [mailto:peter.brawley@stripped]
Sent: lundi 25 avril 2005 22:17
To: Jeff McKeon
Cc: mysql@stripped
Subject: Re: Query question

Jeff,

Then do it with 2 queries,

SELECT @d := MAX( datestamp )
FROM table2
WHERE parentID = X;

SELECT * FROM table2 WHERE parentID = X AND datestamp=@d;PB

-----

Jeff McKeon wrote:

Thanks all but I don't have a mysql version high enough for subqueries.

Thanks,

Jeff

-----Original Message-----
From: Peter Brawley [mailto:peter.brawley@stripped] 
Sent: Monday, April 25, 2005 4:01 PM
To: Jeff McKeon
Cc: mysql@stripped
Subject: Re: Query question


Jeff,

Something like ...

SELECT *
FROM table2 AS a
WHERE datestamp = (
  SELECT MAX( b.datestamp )
  FROM table2 AS b
  WHERE a.parentID = b.parentID
);

PB

-----


Jeff McKeon wrote:

I have a table that contains records that link back to a

main talbe in

a many to one configuration linked by table1.id = table2.parentid

Table1 (one)
Table2 (many)

I want to pull the latest records from table2 for each

record in table1

where certain criteria applie.

So, if record 100 in table1 links to 5 corresponding records

in table2,

I want to pull the latest record from table2 where table2.parentid = 
100 and table2.user not like 'john'

There is a datestamp field in table2.

I just can't figure out how to do this.

Thanks,

Jeff

-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005


No virus found in this incoming message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005

--------------030702010509000704010800-- --=======AVGMAIL-426D560463F6======= Content-Type: text/plain; x-avg=cert; charset=us-ascii Content-Transfer-Encoding: quoted-printable Content-Disposition: inline Content-Description: "AVG certification" No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.10.2 - Release Date: 4/21/2005 --=======AVGMAIL-426D560463F6=======--