Tricky SQL query

[eluser]Dan Murfitt[/eluser]
I need the GROUP BY so that the results only return unique conversations, otherwise it will return the conversation relating to every posts, and since there are many posts per conversations you get duplicate results.

I've done a bit more searching and I seem to have found a possible solution:

Code:

SELECT conversations.*,COUNT(*) AS posts
FROM (SELECT * from conversations_posts ORDER BY created DESC) AS conversations_posts
JOIN conversations ON conversations_posts.conversationid = conversations.conversationid
WHERE conversations.teamid = 18
GROUP BY conversationid
ORDER BY conversations_posts.created DESC

By doing this it seems I am able to run the order before the group as well as after.

Thanks for your help