SitePoint Sponsor

User Tag List

Results 1 to 5 of 5
  1. #1
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Location
    Hessle, East Yorkshire UK
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    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

  2. #2
    SitePoint Enthusiast alexson's Avatar
    Join Date
    Jun 2010
    Posts
    46
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    can you add 'ORDER BY site_rank.at DESC' at the end of the query?

  3. #3
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Location
    Hessle, East Yorkshire UK
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,340
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    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
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Enthusiast
    Join Date
    Jan 2008
    Location
    Hessle, East Yorkshire UK
    Posts
    88
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Perfect thank you r937 works a charm


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •