Find row with maximum value of id in MySQL

aggregate-functions, max, mysql

Solution

You need a sub query here:

SELECT a.id, a.version
FROM articles a
WHERE a.version = (
    SELECT MAX(version)
    FROM articles b
    WHERE b.articleId = a.articleId
)

Problem

Take a look at the MySQL table below called "Articles": ``` +----+-----------+---------+------------------------+--------------------------+ | id | articleId | version | title | content | +----+-----------+---------+------------------------+--------------------------+ | 1 | 1 | 0.0 | ArticleNo.1 title v0.0 | ArticleNo.1 content v0.0 | | 2 | 1 | 1.0 | ArticleNo.1 title v1.0 | ArticleNo.1 content v1.0 | | 3 | 1 | 1.5 | ArticleNo.1 title v1.5 | ArticleNo.1 content v1.5 | | 4 | 1 | 2.0 | ArticleNo.1 title v2.0 | ArticleNo.1 content v2.0 | | 5 | 2 | 1.0 | ArticleNo.2 title v1.0 | ArticleNo.2 content v1.0 | | 6 | 2 | 2.0 | ArticleNo.2 title v2.0 | ArticleNo.2 content v2.0 | +----+-----------+---------+------------------------+--------------------------+ ``` Im trying to come up with a query to return Articles.id where Articles.version is the maximum number. The actual Articles table contains over 10,000 entries. So in this example I ONLY want Articles.id 4 and 6 to be returned. Ive been looking at keyword distinct and function max() but cant seem to nail it. Any suggestions appreciated...

Original source