Query possible sub query question

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

SELECT site_rank.at
     , site.id
     , site.url
     , site_rank.siteid
     , site_rank.rank
  FROM site
INNER
  JOIN ( SELECT siteid
              , MAX(at) AS latest
           FROM site_rank
         GROUP
             BY siteid ) AS m
    ON m.siteid = site.id
INNER
  JOIN site_rank 
    ON site_rank.siteid = m.siteid
   AND site_rank.at = m.latest

:cool:

can you add ‘ORDER BY site_rank.at DESC’ at the end of the query?

No I tried that but ranks are given by users so if two users ranked the same site before any other sites then that site would show twice, and that would also assume I could get the number of sites there are as well

Perfect thank you r937 works a charm