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:

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


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


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