I have a scenario, which is seemingly simple on paper, but I'm having trouble getting to work as desired in practice.
I have two tables (only relevant columns presented):
| Thread
+----------
| ThreadID
| Post
+----------
| PostID
| ThreadID
| Posted (Datetime)
Now, what I want to do, is to join Thread and Post, grouping by ThreadID. But I want to order by Post.Posted in descending order. In plain english, I want to join Thread on the most recent Post relating to it.
My SQL so far:
SELECT Thread.ThreadID, Post.PostID, Post.Created
FROM Thread
LEFT JOIN Post ON Post.ThreadID = Thread.ThreadID
GROUP BY Thread.ThreadID
ORDER BY Post.Created DESC
It does the job, but the order of the join is not correct, as the ordering is, of course, applied only after the grouping is performed.
Surely, there must be a way to accomplish what I need?