group by, order by, with join
- by Scarface
Hey guys, quick question, I have this query, and I am trying to get the latest comment for each topic and then sort those results in descending order (therefore one comment per topic). I have what I think should work, but my join always messes my results up. Somehow, it seems to have sorted the end results properly, but has not taken the latest comment from each topic instead it seems to have just taken a random comment. If anyone has any ideas, would really appreciate any advice
SELECT * FROM comments JOIN topic ON topic.topic_id=comments.topic_id WHERE topic.creator='admin' GROUP BY comments.topic_id ORDER BY comments.time DESC
table comments is structured like
id time user message topic_id
table topic is structured like
topic_id subject_id topic_title creator timestamp description