MySQL grouping isn't respecting ORDER BY

Question

I'm trying to do a MySQL query to find the most recently active threads (and the most recent comment on each thread) in a web forum. Threads are stored in two tables, forum_topics and forum_responses, where each forum_topic has many forum_responses.

Here, I do a search on forum_reponses joined to forum_topics, with a descending sort on forum_response.id:

select t.id, t.title, r.id, r.body 
    from forum_responses r 
    inner join forum_topics t on (r.forum_topic_id = t.id) 
    order by r.id desc;

+----+--------------+----+----------------------------------+
| id | title        | id | body                             |
+----+--------------+----+----------------------------------+
| 17 | New Topic    | 69 | yes                              |
| 19 | Test Topic 1 | 68 | This is a test                   |
| 17 | New Topic    | 64 | hey yo                           |
| 19 | Test Topic 1 | 63 | Test Topic Starter               |
| 18 | Test Topic   | 62 | Test.                            |
| 18 | Test Topic   | 61 | Test                             |
| 17 | New Topic    | 60 | Another test response.           |
| 17 | New Topic    | 59 | Test response.                   |
| 17 | New Topic    | 54 | What should this topic be about? |
+----+--------------+----+----------------------------------+

OK, so far so good. But it's returning duplicates - I just want to have the most recently-responded-to forum topics. So I add a GROUP BY to my query so we can group by the topic ID:

select t.id, t.title, r.id, r.body 
    from forum_responses r 
    inner join forum_topics t on (r.forum_topic_id = t.id) 
    group by t.id 
    order by r.id desc;

+----+--------------+----+----------------------------------+
| id | title        | id | body                             |
+----+--------------+----+----------------------------------+
| 19 | Test Topic 1 | 63 | Test Topic Starter               |
| 18 | Test Topic   | 61 | Test                             |
| 17 | New Topic    | 54 | What should this topic be about? |
+----+--------------+----+----------------------------------+

But now, we have a problem: It's grouping by the forum topic ID, but counterintuitively, we are not getting our forum topics sorted by most recent activity, and the associated forum responses are not the most recent.

What's going wrong here? Is there a way to alter this query so that I get a list of the most recently-contributed-to forum topics, along with their respective most recent comments?

Answer 1

This is what I was suggesting in my initial comment; you need to GROUP separately like so:

SELECT t.id, t.title, r2.id, r2.body 
FROM (
   SELECT forum_topic_id, MAX(id) AS lastResponseID 
   FROM forum_responses 
   GROUP BY forum_topic_id
) AS r
INNER JOIN forum_responses AS r2 
   ON r.forum_topic_id = r2.forum_topic_id AND r.lastResponseID = r2.id
INNER JOIN forum_topics AS t 
   ON r.forum_topic_id = t.id
ORDER BY t.id;

It might be faster (better able to take advantage of indexing) to include forum_topics (and it's title field) in the subquery, but that greatly depends on data distribution; joining millions of indexed rows (every response ever to their topic) can be slower than a relatively few unindexed ones (the most recent responses to their topic).