MySQL having trouble sorting a query correctly
This is for a custom forum I am working on. i need to select all threads, get the number of posts in each thread and also the last post in each thread. I can get the number of posts, but my query is returning the first post rather than the last.
SELECT thread_id, thread_title, p.*, COUNT(p.post_id) AS Posts
FROM forums_threads
JOIN开发者_JAVA技巧 forums_posts AS p ON post_thread_id=thread_id
WHERE thread_forum_id=84
GROUP BY thread_id
ORDER BY thread_date DESC, post_date DESC
As @mixman suggests, you need to link to forum_posts twice: once to get the aggregate amounts (post count and maximum post date), and once to get the actual content of that last post (and I'm assuming that by "last" you mean "most recent") in the thread:
SELECT ft.thread_id, ft.thread_title, fp.*, pmin.postcount
FROM forums_threads AS ft
JOIN (
SELECT post_thread_id, MAX(post_date), COUNT(post_id) AS postcount
FROM forums_posts
GROUP BY post_thread_id
) AS pmin ON ft.thread_id=pmin.post_thread_id
JOIN forums_posts AS fp ON fp.post_thread_id=pmin.post_thread_id AND fp.post_date = pmin.post_date
WHERE ft.thread_forum_id=84
ORDER BY ft.thread_date DESC
Grouping happens before ordering as the SQL suggests. Ordering is then done against the whole result, returning the first thread_date for a grouped thread_id. Restructuring of the SQL with subqueries/self-joins should get the job done.
精彩评论