Join row with specified field value and highest value in other field

I have 2 tables:

Articles:

ID INTEGER

...

Article_versions:

article INTEGER

lang ENUM

time TIMESTAMP

PRIMARY KEY( article,lang,time )

...

I want to select fields from Articles and fields from related latest row Article_versions.

Should i use join or 2 select statements?

And how can i do thi开发者_JAVA百科s?

It is confusing exactly what information you are trying to retrieve but if you know a particular ID then you can use:

SELECT ID,lang,time
FROM articles,article_versions
WHERE articles.ID=<article_id_you_provide> AND
      articles.ID=article_versions.article AND
      article_versions.time = (SELECT max(time)
                               FROM article_versions
                               WHERE article=<article_id_you_provide>);

Let me know if this isn't what you are looking for and I can revise my answer further.

Assuming [Articles_versions].[time] would be the only unique column returned in the join set you could use the following:

SELECT [Articles].[ID], [Articles_versions].[article], [Articles_versions].[lang], MAX([Articles_versions].[time])
FROM Articles Inner Join Articles_versions ON [Articles].[ID] = [Articles_versions].[article]
GROUP BY [Articles].[ID], [Articles_versions].[article], [Articles_versions].[lang]