Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New Coder
    Join Date
    Mar 2004
    Posts
    95
    Thanks
    8
    Thanked 0 Times in 0 Posts

    I need help with ORDER BY date

    Hi,

    I have a table of messages, the structure could probably be better but I have to work with it as it is. The table has these fields:-
    Code:
      
      `id` int(16) NOT NULL AUTO_INCREMENT,
      `to_id` int(16) NOT NULL,
      `from_id` int(16) NOT NULL,
      `original_message_id` int(16) NOT NULL DEFAULT '0',
      `message` text NOT NULL,
      `date` datetime NOT NULL,
    It holds messages between members, when a message is originally stored a column `original_message_id` = 0. When someone replies to a message, then `original_message_id` will equal the `id` of the original message.

    I need to list the original messages, ie `original_message_id` = 0, by date DESC, but I want the date to include the replies. So if a message was sent ages ago, but it just receives a reply I want it listed above messages sent a short time ago.

    I have no idea how to do this. If anyone can help I would greatly appreciate it.

  • #2
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Hmmm...

    Off the top of my head, untested:
    Code:
    SELECT M1.id, M1.to_id, M1.from_id, M1.message, M1.`date` AS originalMessageDate,
           MAX( IFNULL(M2.`date`,M1.`date`) )AS lastReplyDate
    FROM messages AS M1 LEFT JOIN messages AS M2
    ON M1.id = M2.original_messsage_id
    GROUP BY M1.id, M1.to_id, M1.from_id, M1.message, M1.`date1
    ORDER BY lastReplyDate DESC
    If you want to also pick up other fields from M2 (the latest reply) then it gets a bit more complex.
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Taipan (12-08-2012)

  • #3
    New Coder
    Join Date
    Mar 2004
    Posts
    95
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Hmmm...

    Off the top of my head, untested:
    Code:
    SELECT M1.id, M1.to_id, M1.from_id, M1.message, M1.`date` AS originalMessageDate,
           MAX( IFNULL(M2.`date`,M1.`date`) )AS lastReplyDate
    FROM messages AS M1 LEFT JOIN messages AS M2
    ON M1.id = M2.original_messsage_id
    GROUP BY M1.id, M1.to_id, M1.from_id, M1.message, M1.`date1
    ORDER BY lastReplyDate DESC
    If you want to also pick up other fields from M2 (the latest reply) then it gets a bit more complex.
    That is great, thanks so much.

    I know I can filter the results as I retrieve them but would there some way to limit the return list to only show ones where original_messsage_id = 0? I only want to retrieve original messages, but in order of latest reply if there is one.

    Appreciate the help.

  • #4
    Supreme Master coder! Old Pedant's Avatar
    Join Date
    Feb 2009
    Posts
    27,700
    Thanks
    80
    Thanked 4,658 Times in 4,620 Posts
    Oh, sure...

    Just add that as WHERE.

    Code:
    SELECT M1.id, M1.to_id, M1.from_id, M1.message, M1.`date` AS originalMessageDate,
           MAX( IFNULL(M2.`date`,M1.`date`) )AS lastReplyDate
    FROM messages AS M1 LEFT JOIN messages AS M2
    ON M1.id = M2.original_messsage_id
    WHERE M1.original_message_id = 0
    GROUP BY M1.id, M1.to_id, M1.from_id, M1.message, M1.`date1
    ORDER BY lastReplyDate DESC
    An optimist sees the glass as half full.
    A pessimist sees the glass as half empty.
    A realist drinks it no matter how much there is.

  • Users who have thanked Old Pedant for this post:

    Taipan (12-08-2012)

  • #5
    New Coder
    Join Date
    Mar 2004
    Posts
    95
    Thanks
    8
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by Old Pedant View Post
    Oh, sure...

    Just add that as WHERE.

    Code:
    SELECT M1.id, M1.to_id, M1.from_id, M1.message, M1.`date` AS originalMessageDate,
           MAX( IFNULL(M2.`date`,M1.`date`) )AS lastReplyDate
    FROM messages AS M1 LEFT JOIN messages AS M2
    ON M1.id = M2.original_messsage_id
    WHERE M1.original_message_id = 0
    GROUP BY M1.id, M1.to_id, M1.from_id, M1.message, M1.`date1
    ORDER BY lastReplyDate DESC
    Thanks, I did try that and it didn't work, I must have made a typo. Appreciated.

  • #6
    New to the CF scene
    Join Date
    Dec 2012
    Location
    Plano,Texas
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    SELECT M1.id, M1.to_id, M1.from_id, M1.message, M1.`date` AS originalMessageDate,
    MAX( IFNULL(M2.`date`,M1.`date`) )AS lastReplyDate
    FROM messages AS M1 LEFT JOIN messages AS M2
    ON M1.id = M2.original_messsage_id
    GROUP BY M1.id, M1.to_id, M1.from_id, M1.message, M1.`date1
    ORDER BY lastReplyDate DESC

    you may try this code..it may solve your problem

    covetus


  •  

    Posting Permissions

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