Help w/ JOIN - Require 1 row from ony-to-many for each unique id

[SoreGums]

MYSQL 5.0

I have 2 tables.
One full of stuff related to apple trees
The other keeps a log of what happens to the apple trees.
So when I change the status of an apple try i make a row in the log saying the status changed for this apple tree from to and when and by who.

SO what I want to do is create a VIEW of the most recent log entry for each apple tree thus creating a LastModified table. I know I could add a field "ModifiedWhen" & "ModifiedBy" but since this is a made up scenario that best describes my real much more complex scenario. I'm interested in way I've described more then how to best do this simplistic example.

Ok now to put all this visually....


----------------------------
Apples
----------------------------
ID | Status | Color
----------------------------
1 | Fully Grown | Red
2 | Just Planted | Green
3 | Seedling | Green
4 | Seedling | Red

-----------------------------------------------------------------------------------
ApplesLoG
-----------------------------------------------------------------------------------
ID | R_A_ID | Who | When | Notes
-----------------------------------------------------------------------------------
1 | 1 | John | 2007-05-06 | Planted
2 | 1 | Tim | 2007-05-12 | Seedling Now
3 | 2 | John | 2007-05-13 | Just Planted
4 | 1 | Tim | 2007-05-14 | Got some leaves
5 | 3 | Tim | 2007-05-15 | Planted
6 | 4 | John | 2007-05-16 | Planted
7 | 1 | Tim | 2007-05-16 | eh, its a Red one now, thought it was green...
8 | 1 | Tim | 2007-05-17 | Fully Grown apple try now - yum!
9 | 3 | Tim | 2007-05-19 | Seedling Now
10| 4 | John | 2007-05-20 | Seedling Now

-----------------------------
ApplesModified (View)
-----------------------------
ApplesID | Who | When
-----------------------------
1 | Tim | 2007-05-17
2 | John | 2007-05-13
3 | Tim | 2007-05-19
4 | John | 2007-05-20

There that sums up pretty good what I'm trying to do. Just select the most recent entry from the Log table as it relates to the Apples.
My query building so far has got me all the log results as a outer join, then I try limiting it and I end up with 1 result and thats it....

Please help :D

[bubbisthedog]

What does your query look like so far?

[bubbisthedog]

This may do what you're looking for:

select ta.max_appid, Who, ta.max_date
from ApplesLoG
join
(select R_A_ID as max_appid, max(When) as max_date
from ApplesLoG
group by R_A_ID) ta
on ta.max_appid = ApplesLoG.R_A_ID
and ta.max_date = ApplesLoG.When

Note that if there are multiple entries for the same ID on the same date, that the query should return all of them.

[SoreGums]

Note that if there are multiple entries for the same ID on the same date, that the query should return all of them.
Thanks, Will give that a go - yeah the date is actually a TIMESTAMP field, I just made it date so as to save me typing more in the example :)

I knew would have to involve sub queries, just my sub query skillz are lacking...

I could do this problem in a for loop in the program of course but the whole point of having a proper dbms is kinda to off load some of this to it ;)

[mattyblah]

nm queston answered.

[SoreGums]

MYSQL 5.0 as mentioned at the very top - i read the sticky - it said mention this ;)