Find row with maximum value of id in MySQL

Question

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...

Answer 1

From the MySQL manual, this does the trick:

SELECT a1.id
FROM Articles a1
LEFT JOIN Articles a2 ON a1.articleId = a2.articleId AND a1.version < a2.version
WHERE a2.articleId IS NULL;

From the linked manual:

The LEFT JOIN works on the basis that when s1.price a1.version is at its maximum minimum value, there is no s2.price a2.version with a greater lesser value and thus the corresponding s2.article a2.articleId value is NULL.

(I edited to make their text match your situation.)

Answer 2

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
)