Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: Select record with MAX(field) via join

    Again, I'm sure there's a proper name for this... errr... technique? But I have no idea what it is so you're stuck with my wobbly description. Sorry

    Lets' say I've got two tables: tblFiles, and tblFileTitles

    tblFiles:
    id
    file_title_id
    url
    version_number


    tblFileTitles:
    id
    title


    You get the gist: it's a file versioning system. But how do I get the latest version of each file (or rather, file TITLE)?

    I tried this:

    SELECT
    f.id AS fileID,
    MAX(f.version_number) AS latestVersion,
    f.url
    ft.id AS titleID,
    ft.title
    FROM tblFiles f
    INNER JOIN tblFileTitles ft ON f.file_title_id = ft.id
    GROUP BY ft.id

    Which would be great except that the file ID (and URL) that it's pulling out isn't the one that corresponds to the latest version. In fact, it seems to be pulling out the first record from tblFiles that has the corresponding title_id.

    Am I making sense? Can someone explain how to get the "latest" file in there?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    since you asked "via join" --
    Code:
    SELECT f.id
         , f.file_title_id
         , f.url
         , f.version_number
      FROM tblFiles f
    INNER 
      JOIN (
           SELECT file_title_id
                , max(version_number) as max_version
             FROM tblFiles 
           GROUP
               BY file_title_id
           ) as m
        ON m.file_title_id = f.file_title_id
       AND m.max_version   = f.version_number
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Thanks - that's exactly the job I've got to ask, though - it sounds like there's a better way?

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    better? not necessarily
    Code:
    SELECT f.id
         , f.file_title_id
         , f.url
         , f.version_number
      FROM tblFiles f
     WHERE f.version_number =
           ( SELECT max(version_number)
               FROM tblFiles 
              WHERE file_title_id = f.file_title_id )
    to me, it's a bit easier to understand what this query is doing, with its correlated subquery, as compared to the one with the join

    next, you're going to ask which is more efficient, aren't you

    the answer is: you need to test them yourself
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jan 2002
    Posts
    189
    Quote Originally Posted by r937
    correlated subquery
    That's it! I knew there was a trick you could do with a subquery, but could never get my head round joining it to the parent. I should really get myself some sort of theoretical grounding in this, shouldn't I? Thank you, though

Posting Permissions

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