# Thread: complex group by order problem

1. Registered User
Join Date
Apr 2011
Posts
1

## Unanswered: complex group by order problem

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

CREATE TABLE IF NOT EXISTS `messages` (
`MessageID` int(11) NOT NULL AUTO_INCREMENT,
`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]

I get this result:

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?

2. SQL Consultant
Join Date
Apr 2002
Location
Posts
20,002
Code:
```SELECT ms.FromID
, ms.ToID
, SUBSTRING(ms.Message,1,100) AS message
, ms.Viewed
, ms.Date
, MAX(Date) AS latest
FROM messages
GROUP
INNER
JOIN messages AS z
AND z.Date = x.latest
INNER
JOIN messages AS ms
AND [MEMBERID] IN ( ms.ToID, ms.FromID )
GROUP

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•