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.



I think you have to use JOIN.

Written by webarto

Accepted Answer

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 
This page was build to provide you fast access to the question and the direct accepted answer.
The content is written by members of the community.
It is licensed under cc-wiki