Inner ordering of GROUP BY

mysql group order

[eoinoc333]

Hi,

I have the following query:

SELECT topic_id
FROM posts
WHERE poster_id = 2
GROUP BY topic_id
ORDER BY post_time DESC
LIMIT 10

It groups posts from a forum by their topic_id. There may be multiple posts by user 2 per topic. As it is now, the result seems to sort the topics by the FIRST post of the user in each topic. But I want it to sort the topics according to the LAST post by the user.

I figure that this thread is relevant, but cannot see how to apply it to my case:
[webmasterworld.com...]

Edit: I should mention that I'm using MySQl 4.0.27.

Thanks for any help.
Eoin

[LifeinAsia]

Try:

SELECT topic_id
FROM posts
WHERE poster_id = 2
GROUP BY topic_id
ORDER BY topic_id, post_time DESC
LIMIT 10

[eoinoc333]

Thanks. That sorts it with the first topics at the top, I wanted topic with latest post at the top. I appreciate your suggestion.

I had to use an inner SELECT in the end.

If anyone's interested, here is the bigger code, which uses the inner SELECT to order results:

select a.topic_id, t.topic_title, count(p.post_id)-1 as cnt_replies, a.lastpost, p2.post_time
from (
select topic_id, max(post_id) lastpost
from phpbb_posts
where poster_id = 2
group by topic_id
order by lastpost desc
limit 5) a, phpbb_posts p, phpbb_posts p2, phpbb_topics t
where a.topic_id = p.topic_id
and p.post_id >= a.lastpost
and t.topic_id = a.topic_id
and p2.post_id = a.lastpost
group by a.topic_id
order by p.post_id desc