sql - Reading most recent values in a join without performing a nested SELECT -

in interest of eliminating many nested queries possible, i'm trying optimize following query:

   select fp.id,            fp.user_id,            count(c.id) num_replies,            c2.created latest_activity_time, c2.user_id latest_activity_user_id      forum_posts fp left join comments c on c.object_id = fp.id left join (select created, user_id              comments           order created desc              limit 1) c2 on fp.id = c2.object_id     fp.deleted != 1  group fp.id 

sorry if find errors...i tried whittling query down relevant parts , might have made mistake somewhere in process

so basically, have here table of forum posts , table of comment replies posts. each forum post can have multiple replies. first join used counting total number of replies, , second join used recent reply's information. query this:

so, i'm trying figure out how can without having resort nested query. guys can provide extremely useful.

thanks!

edit: i've adjusted query reflect fact need pull not latest_activity_time, latest_activity_user_id. sorry confusion!

use:

   select fp.id,           fp.user_id,           count(a.id) num_replies,           c.date latest_activity_time,           c.user_id latest_activity_user_id      forum_posts fp left join comments on a.object_id = fp.id          left join (select x.object_id,                   x.date,                    x.user_id              comments x              join (select t.object_id,                           max(t.date) max_date                      comments t                  group t.object_id) y on y.object_id = x.object_id                                         , y.max_date = x.date) b on b.object_id = fp.id     fp.deleted != 1  group fp.id