CodingForums.com

CodingForums.com (http://www.codingforums.com/index.php)
-   MySQL (http://www.codingforums.com/forumdisplay.php?f=7)
-   -   I need help with ORDER BY date (http://www.codingforums.com/showthread.php?t=283739)

Taipan 12-07-2012 11:54 AM
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.

Old Pedant 12-07-2012 07:25 PM
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.

Taipan 12-07-2012 11:15 PM
Quote:
Originally Posted by Old Pedant (Post 1298124)
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.

Old Pedant 12-08-2012 12:26 AM
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

Taipan 12-08-2012 09:47 AM
Quote:
Originally Posted by Old Pedant (Post 1298217)
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.

covetus3590 12-10-2012 07:48 AM
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


All times are GMT +1. The time now is 11:00 AM.

Powered by vBulletin®
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.