Complex Group By Ordering problem

[zander1983]

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 Viewed ReplyToID Date MessagesInThread

39 40 reply 5 1 50 2011-04-23 13:59:33 6

39 40 reply 2         1 53 2011-04-23 13:59:19 3

39 40 reply 3 0 54 2011-04-23 13:59:51 3

39 40 start 1         1 60 2011-04-23 14:00:06 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?

[kickstart]

Hi

 

Using group by like that any non agregate non group by fields will be from a random row.

 

However think something like this will do it for you. Using a subselect to get the number of records in each thread and the date / time of the latest reply, and that is JOINed to the messages to get the other details.

 

Not tested and I might have misunderstood what you wanted but hope it will give you an idea.

 

SELECT messages.FromID, messages.ToID, SUBSTRING(messages.Message,1,100), messages.Viewed, messages.ReplyToID, messages.Date, Latest.MessagesInThread 
FROM messages
INNER JOIN (SELECT ReplyToID, MAX(Date), COUNT(*) AS MessagesInThread FROM messages GROUP BY ReplyToID) Latest
ON messages.ReplyToID = Latest.ReplyToID AND messages.Date = Latest.Date

 

All the best

 

Keith