I have a table 'messages' storing messages between users:

CREATE TABLE IF NOT EXISTS `messages` (
`MessageID` int(11) NOT NULL AUTO_INCREMENT,
`ReplyToID` int(11) NOT NULL,
`FromID` int(11) NOT NULL,
`ToID` int(11) NOT NULL,
`Message` varchar(1000) NOT NULL,
`Date` datetime NOT NULL,
`Viewed` tinyint(1) NOT NULL DEFAULT '0',
PRIMARY KEY (`MessageID`)
)

and here is some test data

INSERT INTO `messages` (`MessageID`, `ReplyToID`, `FromID`, `ToID`, `Message`, `Date`, `Viewed`) VALUES
(59, 54, 39, 40, 'reply 3', '2011-04-23 13:59:51', 0),
(58, 50, 39, 40, 'reply 5', '2011-04-23 13:59:33', 1),
(57, 53, 39, 40, 'reply 2', '2011-04-23 13:59:19', 1),
(56, 50, 40, 39, 'reply 4', '2011-04-23 13:54:12', 1),
(55, 54, 40, 39, 'reply 2', '2011-04-23 13:53:49', 1),
(53, 53, 39, 40, 'start 1', '2011-04-23 13:52:43', 1),
(54, 54, 39, 40, 'start 1', '2011-04-23 13:53:00', 1),
(52, 50, 39, 40, 'reply 3', '2011-04-23 13:52:26', 1),
(51, 50, 40, 39, 'reply 2', '2011-04-23 13:51:41', 1),
(50, 50, 40, 39, 'start 1', '2011-04-23 13:51:27', 1),
(60, 60, 39, 40, 'start 1', '2011-04-23 14:00:06', 1),
(61, 53, 40, 39, 'reply 3', '2011-04-23 14:00:36', 0),
(62, 60, 40, 39, 'reply 2', '2011-04-23 14:01:01', 1),
(63, 50, 40, 39, 'reply 6', '2011-04-23 14:01:57', 0);

The ReplyToID allows me to track threads between 2 users. I want a query which will output a list of threads for the logged on user. Here's my query:

SELECT ms.FromID, ms.ToID, (Select SUBSTRING(a.Message,1,100) from Messages a WHERE a.MessageID = ms.MessageID order by a.Date Desc) As Message, ms.Viewed, ms.ReplyToID, ms.Date, count(ReplyToID) as MessagesInThread from messages ms
left join member m on (m.MemberID = ms.FromID)
left join member mm on (mm.MemberID = ms.ToID)
where ToID = [MEMBERID] or FromID = [MEMBERID]
Group by ReplyToID


I get this result:

FromID ToID Message ReplyToID Date MessagesInThread
39 40 reply 5 50 2011-04-23 6
39 40 reply 2 53 2011-04-23 3
39 40 reply 3 54 2011-04-23 3
39 40 start 1 60 2011-04-23 2


I want the last message in the thread to be output, so the first row it should have a message of 'reply 6' as there are 6 messages in the thread.
Instead, its out putting the message value of the second last message in the thread. The same with row 2 and 4. Im not getting the value of the last message
of the thread.....hope someone understands this! any help?