MySQL: Get last forum post date

[eluser]mironcho[/eluser]
One approach is to use such sub select (sorry - query is not translated into CI AR)

Code:

SELECT
    f.*,
    (SELECT post_date FROM forum_posts WHERE post_id = f.post_id OR post_parent_id = f.post_id ORDER BY post_date DESC LIMIT 1) AS last_post_date
FROM
    forum_posts AS f
WHERE
    post_parent_id = '0'
    AND post_cat_id = $category_id
ORDER BY
    last_post_date DESC

Other, faster, solution is to use additional field in your table - last_post_date. Just update it in "master post" whenever there is a change in it's thread.

Hope this helps.
Miro