I need help with ORDER BY date

Taipan · 12-07-2012, 11:54 AM

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

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

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