aggregate function

[flyadvertising]

The query (B) below is working, but in the first LEFT JOIN I'd like to pull the most recent result. So, I'd have to add something like the query (A) below in somehow.

A
SELECT poArID, poDatetime FROM posts ORDER BY poDatetime DESC

B
SELECT articles.arID, articles.arType, articles.arTitle, articles.arDescr, articles.arCl, articles.arURL, posts.poTxt, posts.poDatetime, posts.poAd, posts.poArID, advisors.adLname, advisors.adID, advisors.adFname, advisors.adTitle
FROM articles
LEFT JOIN posts ON articles.arID = posts.poArID
LEFT JOIN advisors ON posts.poAd = advisors.adID
WHERE arCl = '" . $clID . "'
GROUP BY articles.arTitle
ORDER BY articles.arTitle ASC ";

 

[r937]

SELECT articles.arID
     , articles.arType
     , articles.arTitle
     , articles.arDescr
     , articles.arCl
     , articles.arURL
     , posts.poTxt
     , posts.poDatetime
     , posts.poAd
     , posts.poArID
     , advisors.adLname
     , advisors.adID
     , advisors.adFname
     , advisors.adTitle
  FROM articles 
[red]LEFT OUTER
  JOIN ( SELECT poArID
              , MAX(poDatetime) AS latest
           FROM posts 
         GROUP
             BY poArID ) AS m
    ON m.poArID = articles.arID[/red]
LEFT OUTER
  JOIN posts 
    ON posts.poArID = articles.arID
   [red]AND posts.poDatetime = m.latest[/red]
LEFT OUTER
  JOIN advisors 
    ON advisors.adID = posts.poAd
 WHERE articles.arCl = ". $clID ." [blue]-- no single quotes[/blue]
GROUP 
    BY articles.arTitle 
ORDER 
    BY articles.arTitle ASC

r937.com | rudy.ca
Buy my new book Simply SQL from Amazon