Results 1 to 2 of 2
  1. #1
    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,
    `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?

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Code:
    SELECT ms.FromID
         , ms.ToID
         , SUBSTRING(ms.Message,1,100) AS message
         , ms.Viewed
         , ms.ReplyToID
         , ms.Date
         , COUNT(ReplyToID) AS MessagesInThread
      FROM ( SELECT ReplyToID
                  , MAX(Date) AS latest
               FROM messages
             GROUP
                 BY ReplyToID ) AS x
    INNER
      JOIN messages AS z
        ON z.ReplyToID = x.ReplyToID
       AND z.Date = x.latest
    INNER
      JOIN messages AS ms
        ON ms.ReplyToID = x.ReplyToID
       AND [MEMBERID] IN ( ms.ToID, ms.FromID )
    GROUP 
        BY ms.ReplyToID
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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