Tricky SQL query

[eluser]Dan Murfitt[/eluser]
Thanks for your help everyone

@mdowns - I had considered a lastpost field, as this would definitely do the trick, but I wanted to keep the data normalised as much as possible i.e. by not duplicating any data.

@ehicks727 - Sorry, they're still appearing in the order that the conversations were created in, not the order in which the last posts were created (the order of conversationid should be 2, 4, 3, 1).

@fesweb - I did try that, but the problem seems to be that the group by is always run before the order by. This means that the correct results are removed from the group then the final resultset, although ordered correctly, isn't correct. I guess this is why I need the sub-query.

@uptime and ehicks727 - Yup, I wanted to keep things normalised. I do agree, the userid doesn't need to be included in the conversation, as it can be obtained from the posts. My rationale for this was if the first post was deleted, I would still want to know who started the conversation.

Thanks again to everyone, this is the first real tricky thing I have run into with my CodeIgniter app and I'm glad that the support on the forum is so helpful and friendly Here's some code which seems to work. Conversationid 2 appears at the top because it has the latest created post referenced to it from the conversations_posts table:

Code:

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