List:General Discussion« Previous MessageNext Message »
From:mathias fatene Date:April 25 2005 8:14pm
Subject:RE: Query question
View as plain text  
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:peter.brawley@stripped] 
Sent: lundi 25 avril 2005 22:01
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


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1


Thread
Query questionJeff McKeon25 Apr
  • Re: Query questionSGreen25 Apr
  • RE: Query questionmathias fatene25 Apr
    • RE: Query questionSGreen25 Apr
      • RE: Query questionmathias fatene25 Apr
  • Re: Query questionPeter Brawley25 Apr
    • RE: Query questionmathias fatene25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
    • RE: Query questionmathias fatene25 Apr
      • Re: Query questionPeter Brawley25 Apr
      • RE: Query questionSGreen25 Apr
        • RE: Query questionmathias fatene26 Apr
          • Re: Query questionMartijn Tonies26 Apr
            • RE: Query questionmathias fatene26 Apr
              • Re: Query questionChris Ramsay26 Apr
                • Re: Query questionmfatene26 Apr
  • Re: Query questionPeter Brawley25 Apr
  • RE: Query questionSGreen25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
RE: Query questionJeff McKeon25 Apr
  • Re: Query questionPeter Brawley25 Apr
RE: Query questionJeff McKeon25 Apr