SQL query to get greatest distinct date - kind of...

Question

I have two tables: articles and articletags

articles: id, author, date_time, article_text
articletags: id, tag

(article.id == articletags.id in a many-to-many relationship)

I am after the last time that something was published under each tag. To put it another way, for every tag, look through all the articles it is related to and find the most recent and return that.

eg articles:

1, me, 12 Nov, Sometext
2, me, 13 Nov, Sometext
3, me, 14 Nov, Sometext

article tags

1, foo
1, bar
2, foo
3, bar

I want to get back:

foo, 13 Nov
bar, 14 Nov

I can get as far as an inner join and then am stumped. I dont think a DISTINCT clause is what I am after and I am not familiar enough for subqueries to know if that would help.

SELECT date_time, tag 
FROM articles, articletags
WHERE articles.id = articletags.id

Is this even possible?

Answer 1

SELECT date_time, tag 
FROM articles, articletags
WHERE articles.id = articletags.id
ORDER BY date_time DESC
GROUP BY tag

Answer 2

select t.tag, max(a.date_time) as latest
from articles a
inner join articletags t
on t.id = a.id
group by t.tag