group by, order by, with join

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**

Answer 1

You've got a couple of things going on here. First, the reason your current query is returning weird results is that you aren't really using your GROUP BY clause in the way intended; it is intended to be used with aggregrated fields (like COUNT(), SUM(), etc). It is a convenient side-effect that on MySQL, the GROUP BY clause also returns the first record that would be in the group--which, in your case, should be the first inserted message for each topic (not a random one). So your query as it is written is essentially returning the oldest messsage per topic (on MySql only; note that other RDBMS's will throw an error if you try to use a GROUP BY clause like that!)

But you can actually abuse the GROUP BY clause to get what you want, and you are really close already. What you need to do is to do a sub-query to make a derived table first (with your messages ordered by DESC date), then query the derived table using the GROUP BY clause like this:

select * from (
  SELECT
    topic.topic_title, comments.id, comments.topic_id, comments.message
  FROM comments
  JOIN topic ON topic.topic_id=comments.topic_id
  WHERE topic.creator='admin'
  order by comments.time desc) derived_table
group by topic_id

Answer 2

This is an extension of standard SQL in MySQL which I don't think is helpful at all. In standard SQL your command would not be allowed at all since there's no way to determine which single line should be reported as a result of the GROUP BY. MySQL will execute this command with (as you found out) a random row returned.

You can see a discussion of this issue here: MySQL - Control which row is returned by a group by.