group by, order by, with join

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

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.

Tags:

Mysql