Forum Moderators: open
I have the following query:
SELECT topic_id
FROM posts
WHERE poster_id = 2
GROUP BY topic_id
ORDER BY post_time DESC
LIMIT 10It 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
SELECT topic_id
FROM posts
WHERE poster_id = 2
GROUP BY topic_id
ORDER BY topic_id, post_time DESC
LIMIT 10
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
