Pull latest version of content for all languages in a revisions system

I have a MySQL database which has three database tables: news_articles, languages and news_articles_languages.

• The languages table holds a record for each language the system supports
• The news_articles table holds generic information on a news article, such as ID and an attached image
• The news_articles_languages table contains localized information on a news article, and also stores revisions.

The structure of the news_articles_languages table is as follows:

id            INT       AUTO_INCREMENT
article_id    INT
language_id   INT
title         VARCHAR
friendly_name VARCHAR
content       TEXT
created       TIMESTAMP

When a news article is saved, the translations are inserted into this table with a new created column, so I can easily query previous revisions.

However, I want to be able to pull out all latest revisions based on an article ID. What would the query for this be? I've 开发者_如何学Gotried various combinations of GROUP BY and DISTINCT, but these only give one result in one language; I want all languages for a given article.

If the revision with the highest id is always the latest, then the following should work I think. If you can't depend on that and need to use 'created', then I'm not sure!

SELECT * FROM `news_articles_languages` WHERE id IN(SELECT MAX(id) FROM `news_articles_languages` WHERE article_id = 1 GROUP BY language_id)