Extracting latest version of articles from the results of a previous query

Question

I have the following query:

SELECT e_c.*, c.name, j.status, j.version, j.articleId, j.title FROM assetcategory AS c
    INNER JOIN assetentries_assetcategories AS e_c 
        ON c.categoryId = e_c.categoryId AND c.name = 'news'
    INNER JOIN assetentry AS e
        ON e.entryId = e_c.entryId
    INNER JOIN journalarticle AS j
        ON j.resourcePrimKey = e.classPK
        AND e.classNameId = (SELECT classNameId FROM classname_ WHERE value = 'com.liferay.portlet.journal.model.JournalArticle')
        AND j.companyId= e.companyId
WHERE j.status = 0

which returns all the category news in the journalarticles. From the results I need to select the most recent versions for each articleId. For example suppose there is an article with 4 versions, even with different title, it is the same article because it will have the same articleId. So therefore for each unique articleId I need the latest version. How can I do that?

Answer 1

Add a join to a subquery which finds the most recent version for each article:

SELECT e_c.*, c.name, j1.status, j1.version, j1.articleId, j1.title
FROM assetcategory AS c
INNER JOIN assetentries_assetcategories AS e_c 
    ON c.categoryId = e_c.categoryId AND c.name = 'news'
INNER JOIN assetentry AS e
    ON e.entryId = e_c.entryId
INNER JOIN journalarticle AS j1
    ON j1.resourcePrimKey = e.classPK AND
       e.classNameId = (SELECT classNameId FROM classname_
                   WHERE value = 'com.liferay.portlet.journal.model.JournalArticle') AND
       j.companyId = e.companyId
INNER JOIN
(
    SELECT articleId, MAX(version) AS max_version
    FROM journalarticle
    WHERE status = 0
    GROUP BY articleId
) j2
    ON j1.articleId = j2.articleId AND j1.version = j2.max_version;

The basic idea behind the join to the subquery aliased as j2 above is that it restricts the result set to only the most recent version of each article. We don't necessarily have to change the rest of the query.