views:

90

answers:

2

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?

+1  A: 

Use both the thread id and the created date in the order by clause

SELECT Thread.ThreadID, Post.PostID, Post.Created
FROM Thread
LEFT JOIN Post ON Post.ThreadID = Thread.ThreadID
GROUP BY Thread.ThreadID
ORDER BY Thread.ThreadID, Post.Created DESC 
Benoit Vidis
Thanks, this works. Would you be able to explain this a bit further?
nikc
@nikc: I thought the requirement was *"I want to join Thread on the most **recent** Post"*? This will not necessarily give you the latest. In fact, I don't think that PostID and Created will necessarily refer to the same record in this query.
RedFilter
@OrbMan: yes, that is the requirement. On my limited test data, it seemed to do the trick.
nikc
+2  A: 
select t.ThreadID, p.PostID, p.Posted
from Thread t
inner join (
    select ThreadID, Max(Posted) as MaxPosted
    from Post
    group by ThreadID       
) pm on t. ThreadID = pm.ThreadID
inner join Post p on pm.ThreadID = p.ThreadID and pm.MaxPosted = p.Posted
order by p.Posted desc
RedFilter