select unique values from column but order based on another

Question

I need a unique list of parent_threads based on the desc order of postID, postID is always unique but often the parent_thread field is the same for multiple posts.

So what i need is a list of posts in order they were replied to.

so for example in the image below i need to disregard posts 400 and 399 as they're repeats. i've got a query to work using a subquery but the problem with this subquery is that it can sometimes take up to 1 second to query, i was wondering if there was a more efficient way to do this. i've tried group by and distinct but keep getting the wrong results.

imge of the table

Here is the query that i have which produces the results i want, which is often slow.

SELECT `postID`
FROM `posts`
ORDER BY
(
    SELECT MAX(`postID`)
    FROM `posts` `sub`
    WHERE `sub`.`parent_thread` = `posts`.postID
)
DESC

Answer 1

Your subquery is known as a dependent subquery. They can make queries very slow because they get repeated a lot.

JOIN to your subquery instead. That way it will be used just once, and things will speed up. Try this subquery to generate a list of max post ids, one for each parent thread.

                   SELECT MAX(postID) maxPostID, parent_thread
                     FROM posts
                    GROUP BY parent_thread

Then use it in your main query like this

SELECT posts.postID
FROM posts
LEFT JOIN (
                       SELECT MAX(postID) maxPostID, parent_thread
                         FROM posts
                        GROUP BY parent_thread
     ) m ON posts.postID = m.parent_thread
ORDER BY m.maxPostID DESC