[RESOLVED] Sorting by users last post

[asa_carter]

I am trying to sort topics in a discussion board by order of the user's last post in each topic. So when a user a replies to a post, that topic is displayed at the top in a list of all topics. My SQL below seems to sort by the topics creation date rather than the creation date of the user's last post in the topic.

I've tried searching the SQL docs but can't find an answer. I'm not sure if I need a union to be able to do it.

Tables:

discussionTopics

id | title | region | created

discussionPosts

id | parentId | topicId | region | title | body | author | created

SQL:

SELECT discussionTopics.*
FROM discussionTopics
LEFT JOIN discussionPosts ON discussionTopics.Id = discussionPosts.topicId
WHERE discussionTopics.region = '$region'
GROUP BY discussionTopics.id
ORDER BY discussionPosts.created DESC

[Lars_Berg]

select dt.title, 
          dt.region,
          coalesce(max(dp.created),dt.created) as lastPost
   from discussionTopics dt
   left join discussionPosts dp
      on dt.id = dp.topicId
 where dt.region = '$region'
 group by dt.id
 order by lastPost desc

[asa_carter]

thanks 🙂