Here we are Shawn, With empty tables : +----+--------------------+-------+------+---------------+------+------- --+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+------+---------------+------+------- --+------+------+-------------+ | 1 | PRIMARY | a | ALL | NULL | NULL | NULL | NULL | 0 | Using where | | 2 | DEPENDENT SUBQUERY | b | ALL | NULL | NULL | NULL | NULL | 0 | Using where | +----+--------------------+-------+------+---------------+------+------- --+------+------+-------------+ 2 rows in set (0.00 sec)
mysql> mysql> explain select parentid,max(datestamp) from table2 -> group by parentid; +----+-------------+--------+------+---------------+------+---------+--- ---+------+---------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+--- ---+------+---------------------------------+ | 1 | SIMPLE | table2 | ALL | NULL | NULL | NULL | NULL | 0 | Using temporary; Using filesort | +----+-------------+--------+------+---------------+------+---------+--- ---+------+---------------------------------+ One or two table scans ? Best Regards -------------------- Mathias FATENE Hope that helps *This not an official mysql support answer -----Original Message----- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: lundi 25 avril 2005 22:01 To: Jeff McKeon Cc: mysql@lists.mysql.com 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]