CodingForums.com - View Single Post

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.

12-07-2012, 07:25 PM	PM User \| #2
Old Pedant Supreme Master coder! Join Date: Feb 2009 Posts: 23,247 Thanks: 59 Thanked 3,998 Times in 3,967 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.