mysql从结果中group_MySQL查询在GROUP BY中排序结果

I'm coding a forum system and I'm trying to get the last post in a topic. The problem is I'm grouping the results on the topic id and I can't figure out a way to get the last reply to be displayed in the grouped data.

Here is my query so far:

SELECT SQL_CACHE users.user_id,

users.username,

topics.title,

topics.topic_id,

topics.previews,

topics.date_added,

posts.post_id,

last.username AS last_username,

last.user_id AS last_user_id,

MAX( posts.post_id ) AS last_post_id,

posts.date_added AS last_data

FROM `topics`

LEFT JOIN `users` ON users.user_id = topics.user_id

LEFT JOIN `posts` ON ( posts.topic_id = topics.topic_id )

LEFT JOIN `users` AS last ON ( last.user_id = posts.user_id )

WHERE fcat_id = '2'

GROUP BY topics.topic_id

解决方案

You can do something like

SELECT *, `last`.`user_id` AS last_user_id FROM

(

SELECT users.user_id,

users.username,

topics.title,

topics.topic_id,

topics.previews,

topics.date_added,

posts.post_id,

MAX( posts.post_id ) AS last_post_id,

posts.date_added AS last_data

FROM `topics`

LEFT JOIN `users` ON users.user_id = topics.user_id

LEFT JOIN `posts` ON ( posts.topic_id = topics.topic_id )

WHERE fcat_id = '2'

GROUP BY topics.topic_id

) AS `tst`

LEFT JOIN `posts` ON ( posts.post_id = tst.last_post_id )

LEFT JOIN `users` AS `last` ON ( `last`.user_id = posts.post_id )

Just set your selects properly and maybe alias the posts JOIN which is outside the subquery