Here's my query:
SELECT thread_id, MAX(post_id) as post_id, subject, user_id, username, dateline FROM posts GROUP BY thread_id ORDER BY dateline DESC LIMIT 0,9;
I'm trying to output the last 10 posts on my forum in the following format:
<a href="link_to_latest_post_in_thread">Thread Subject</a> posted by <a href="link_to_user_profile">Username of LAST USER to post in thread</a>
Everything's working fine with the query above, except the username and user_id returned do not always belong to the same row as MAX(post_id) -- they randomly come from any post in the thread. If a thread has 5 posts by 5 different users, I want the output to read "SUBJECT posted by LAST USER TO POST IN SUBJECT" but instead MySQL is (seemingly) randomly selecting the username and user_id value from any of the 5 rows that are being grouped under thread_id.
How do I tell MySQL "Grab the row with the maximum post_id value for each thread_id, and ONLY use values from that row. Don't randomly return values from other rows that are getting grouped under thread_id."
Thanks a lot for your help.
select thread_id, p2.post_id as post_id, subject, user_id, username, dateline from posts join ( select MAX(post_id) as post_id from posts group by thread_id order by dateline DESC limit 10 ) as p2 ON p2.post_id = posts.post_id order by dateline DESC
The content is written by members of the stackoverflow.com community.
It is licensed under cc-wiki