I have a query that I am 50% there with but I am not good enough with SQL to get the rest working:
I have two tables
site - id,url
site_rank - at, rank,siteid
I am using the following query
select site_rank.at, site.id, site.url, site_rank.siteid, site_rank.rank
from site
LEFT JOIN site_rank ON site.id = site_rank.siteid
this gives:
at,id,url,siteid,rank
2010-01-19 09:34:42, 2, site1.co.uk, 2, 2
2010-02-01 00:02:56, 2, site1.co.uk, 2, 1
2010-03-01 00:09:38, 2, site1.co.uk, 2, 2
2010-01-19 09:38:10, 3, site2.co.uk, 3, 5
2010-02-01 00:09:52, 3, site2.co.uk, 3, 4
2010-03-01 00:22:38, 3, site2.co.uk, 3, 6
I need to get a record set containing the latest records (based on the timestamp column site_rank.at) for each site url (site.url)
I assume I need to do a sub-query to find the ‘newest’ timestamp?
Any help would be really gratefully accepted
Matt