group by, order by, with join

Posted by Scarface on Stack Overflow See other posts from Stack Overflow or by Scarface
Published on 2010-04-15T02:10:27Z Indexed on 2010/04/15 2:13 UTC
Read the original article Hit count: 205

Filed under:

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

© Stack Overflow or respective owner

Related posts about mysql