MySQL query question

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

You can use a subquery here:

select
    a.id
from
    articles a
where
    a.version = (select max(version) from articles)

Since this isn't a correlated subquery, it will be pretty fast (as fast or faster than a join), so you don't need to worry about that. But it'll return all of the values that have the max articleid.

Of course, I'd recommend that you throw an index on articleid (assuming id is already clustered). That will help it return even faster.

As noted in the comments, if you want to get the max version for each articleid, you can do the following:

select
    a.id
from
    articles a
    inner join (select articleid, max(version) 
                from articles group by articleid) as b on
        a.articleid = b.articleid
        and a.version = b.version

This will create a join out of that subquery and typically be much faster than a correlated subquery that selects the max for each articleid.

Answer 2

SELECT Articles.id FROM Articles WHERE Articles.version IN (SELECT MAX(Articles.version) FROM Articles)

Answer 3

maybe something like:

select * from articles where articleid in (select max(articleversion) from articles)

Answer 4

You can do

SELECT articles.id
  FROM articles a
 WHERE NOT EXISTS(SELECT * FROM articles a2 WHERE a2.version > a.version)

I don't know MySQL will perform with it, but Oracle and SQL server are fine with it.

Answer 5

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
)

Answer 6

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;

See the link for rationale.