CODEWITHSUNDEEP
phpmysql
Chris
Chris

Reputation: 1333

Having a problem pulling a MAX() value in MySQL

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.

Chris

Upvotes: 2

Views: 319

Answers (1)

Wouter van Nifterick
Wouter van Nifterick

Reputation: 24086

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

Upvotes: 3

Related Questions