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@earthlink.net] 
Sent: Monday, April 25, 2005 4:01 PM
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