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: [email protected]
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]
