I have already demonstrated a working query which orders the results by the p.updated field in descending order. Based on the sample data you provided, the proof is a few posts above this.
select thread.id, thread.title, max(post.updated)
from
threads thread
inner join
posts post
on post.thread_id = thread.thread_id
group by thread.thread_id
order by max(post.updated) desc
mysql> SELECT post.id, thread.title, max(post.updated) FROM Thread thread INNER JOIN Post post ON thread.id = post.thread_id where thread.forum_id = 1 GROUP BY post.thread_id ORDER BY max(post.updated) DESC;
+----+------------+---------------------+
| id | title | max(post.updated) |
+----+------------+---------------------+
| 6 | Dorethy | 2007-09-30 19:40:23 |
| 1 | Pete Burns | 2007-09-05 16:05:35 |
+----+------------+---------------------+
2 rows in set (0.00 sec)
mysql>
What version of mysql you are running? And don't you run it in ANSI mode by any chance?
btw, saying "It doesn't work", or even posting the results alone without posting what you did to get them not helping us help you. Do it like astions did: fire up your mysql command line client, type the query then copy both the query and result and post here.
SELECT p.`id`, t.`title`, MAX(p.`updated`)
FROM `Thread` t
INNER JOIN `Post` p ON (t.`id` = p.`thread_id`)
WHERE t.`forum_id` = $id
GROUP BY p.`thread_id`
ORDER BY MAX(p.`updated`) DESC
EDIT:
If I remove the MAX on the orderby, then the query doesn't error, however the order is once again done by the thread id and not by p.updated :s