Unable to correctly combine Group by and Order by in a join

Question

I have a message table -

msg
_____
id                  int(11)
subject             varchar(255)
content             text
create_time         datetime
parent              int(11)

• parent is a foreign key onto the msg table itself.
• parent can only refer to a row which has parent set to NULL i.e. all replies inside a conversation have a single parent: the first message of the conversation.

I want to be able to select parent messages in descending order of create_time, taking into account also the create_time of their replies, like the order of grouped conversations in Gmail. I also want to join the most recent reply (if one exists) to each parent.

I'm unable to come up with the right SQL. This is what I've done so far -

SELECT * FROM `msg` `parent`
LEFT JOIN `msg` `reply`
ON `reply`.`parent` = `parent`.`id`
WHERE `parent`.`parent` IS NULL
GROUP BY `parent`.`id`
ORDER BY `reply`.`create_time` DESC

Two immediate problems with this are -

1. Parent create time isn't taken into the ORDER BY clause.
2. This is not giving me the least recent reply for a parent.

I'd appreciate a poke in the right direction. Thanks.

Answer 1

ORDER BY `reply`.`create_time` DESC, `parent`.`create_time` DESC

Answer 2

This worked for me -

SELECT  `parent`.`id` AS `pId`
    ,   `parent`.`create_time` AS `pTime`
    ,   `parent`.`subject`
    ,   `parent`.`content` AS `pContent`
    ,   `reply`.`id` AS `rId`
    ,   `reply`.`create_time` AS `rTime`
    ,   `reply`.`content` AS `rContent`
    ,   IF(`reply`.`create_time` IS NULL, `parent`.`create_time`, `reply`.`create_time`) AS `max_time`
FROM    `msg` `parent`
        LEFT JOIN
        `msg` `reply`
        ON `reply`.`parent` = `parent`.`id`
WHERE   `parent`.`parent` IS NULL
ORDER BY `max_time` DESC