Geek Answers Handbook

Find row with max id value in MySQL

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 find a query to return Articles.id, where Articles.version is the maximum number.

The actual article table contains over 10,000 entries.

So, in this example, I ONLY want the articles to return. 4 and 6. I looked at the different keyword and the max () function, but cant seem to nail it.

Any suggestions appreciated ...

+5
source share
7 answers

Here you need the following query:

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

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

+5

:

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

, ( , ), . max articleid.

, articleid (, id ). .

, articleid, :

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

, , , , max articleid.

+4

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

+1

- :

* , articleid in ( max (articleversion) )

+1

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

, MySQL , Oracle SQL- .

+1

DJDonaL3000. , . , . : A B. A , B - . , , , A.

+1

More articles:


All Articles