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
Hyposaurus
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
Gordon bell
source
to share
SELECT date_time, tag
FROM articles, articletags
WHERE articles.id = articletags.id
ORDER BY date_time DESC
GROUP BY tag
0
SoapBox
source
to share