Clever Geek Handbook

SQL query to get great date - kind

I have two tables: articles and articles

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

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

After the last time I posted something under each tag. In other words, for each tag, go through all the articles it is associated with and find the most recent and return them.

for example 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 come back:

foo, 13 Nov
bar, 14 Nov

I can get to the inner join and then dead end. I don't think the DISTINCT clause is what I need, and I'm not familiar enough with subqueries to know if that helps.

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

Is it possible?

0


source to share


2 answers 


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


+4


source 


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


0


source






More articles:


All Articles