Re: query within query

[David White]

This gives me the right records, in order, one of each required, but shows the oldest statdate data rather than the most recent...

- produces similar results with or without the left join.

select
s.site_id,
s.site_name,
DATE_FORMAT(s1.date,'%d %M %Y') as statdate,

from
cp_SITEs as s

INNER JOIN cp_STATs s1 using ( site_id )

left join cp_STATs as s2 on s.site_id=s2.site_id and s1.date > s2.date

where
s.parent_id= 1

group by
s1.site_id

order by
s1.site_id asc, s1.date desc