Tricky SQL query

[eluser]Dan Murfitt[/eluser]
Hi

I was wondering if someone could offer some help with a tricky SQL query - I'm writing a messaging system which operates in a similar way to a forum. Conversations are basically forum threads, and posts are posts within a thread. What I need to be able to do is get the conversations related to a specific team, ordered by the latest updated conversations (determined by the conversation with the latest post 'created'). So, here's the schema:

Conversations table:

Code:

CREATE TABLE `conversations` (
  `conversationid` int(11) NOT NULL auto_increment,
  `teamid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `created` int(11) NOT NULL,
  `topic` varchar(255) NOT NULL,
  PRIMARY KEY  (`conversationid`)
);

Code:

CREATE TABLE `conversations_posts` (
  `postid` int(11) NOT NULL auto_increment,
  `conversationid` int(11) NOT NULL,
  `userid` int(11) NOT NULL,
  `created` int(11) NOT NULL,
  `message` longtext NOT NULL,
  PRIMARY KEY  (`postid`)
);

Here's what I've tried so far (using example teamid 18):

Code:

SELECT conversations.* FROM conversations
JOIN conversations_posts ON conversations.conversationid = conversations_posts.conversationid
WHERE conversations.teamid = 18
GROUP BY conversations_posts.conversationid
ORDER BY conversations_posts.created DESC

This produces a strange result - as soon as I add the GROUP BY (like above) the order becomes broken. Without the GROUP BY they are in the correct order, but there are duplicates as it shows every conversation for every post.

Does anyone know how to remidy this? I just need the conversations, for a particular team, ordered by the latest 'created' date from the joined 'conversations_posts' table. I've tried lots of variations but I just can't get it working. Any help here appreciated

Thanks