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.