Hi Peter
Thanks for your help. I have mused over your suggestion and read through the link you suggested, it is very useful and I will learn some good stuff... It has helped me refine your suggested code a bit... I am pretty sure I have got the initial select and the from bit done. I am struggling with those joins. So I have reprinted your code, thanks, and added my amendments below...
SELECT
s.site_id,s.site_name, etc, ...
DATE_FORMAT(s1.date,'%d %M %Y') as statdate
FROM cp_sites AS s
INNER JOIN cp_stats AS s1 USING( site_id )
LEFT JOIN cp_stats AS s2 ON s1.item_id = s2.item_id AND s1.date < s2.date
WHERE s2.site_id IS NULL
ORDER BY ...
I use cp_STATs in the select, I have followed your lead in the order by. I want to find the oldest statdate for each site_id.
SELECT
s.site_id,s.site_name, cp_STATs.gpages etc, ...
DATE_FORMAT(cp_STATs.date,'%d %M %Y') as statdate
FROM cp_sites AS s
INNER JOIN cp_stats AS s1 USING( site_id )
WHERE
s.parent_id= 1
ORDER BY
s1.site_id asc, s1.date desc
However from a 131 record database I get 131 records when I should be getting 20 roughly. I dont get the 2nd inner join, as modified:
inner join cp_STATs as s2 on s.site_id=s2.site_id and s1.date < s2.date
Following the other examples given on
http://www.artfulsoftware.com/queries.php
I tried this too:
from
cp_SITEs as s
WHERE
s.date = (
SELECT MAX( s.date )
FROM cp_STATs AS s1
WHERE s.item_id = s1.item_id
);
it fails on MAX( s.date )
Thanks
Edited 1 time(s). Last edit at 04/07/2006 05:18PM by David White.