MySQL Sub-query.. Doesn't provide proper information after 3 entries into table.

Posted by Chris Keefer on Stack Overflow See other posts from Stack Overflow or by Chris Keefer
Published on 2010-04-23T19:30:29Z Indexed on 2010/04/23 19:33 UTC
Read the original article Hit count: 273

After I get 3 rows in my forum_threads table this no longer does it's job; to organize a list of active forum threads and put the most recently responded-to thread at the top of the list, followed by second most recent posted-to thread, followed by third, fourth, etc.

Like I said, the query works wonders up until there is a fourth row added to forum_threads.

SELECT forum_threads.*, forum_posts.thread_id FROM forum_threads INNER JOIN (SELECT MAX(id) AS id, thread_id as thread_id FROM forum_posts group by thread_id order by id DESC) forum_posts ON forum_threads.id = forum_posts.thread_id

© Stack Overflow or respective owner

Related posts about mysql

Related posts about subquery