mysql - Order by highest value of two datetime fields from two different tables -

mysql - Order by highest value of two datetime fields from two different tables -

i trying list of topics ordered highest value of 2 dates:

the date of recent post on topic or the date of start of topic

both values in different tables. utilize query in phalcon , 1 works, doesn't want do. may because haven't specified want date of lastly post count (maybe picking first one, not sure)? hope can help me create query work.

$phql = "select forumtopic.* forumtopic left bring together forumtopicpost on forumtopic.id = forumtopicpost.topic_id forumtopic.forum_id = ".$forum->id." grouping forumtopicpost.topic_id order greatest( coalesce(forumtopic.created_at, '0000-00-00 00:00:00'), coalesce(forumtopicpost.created_at, '0000-00-00 00:00:00') ) desc"; $all_topics = $this->modelsmanager->executequery($phql);

this query, aliases simplify understanding it:

select ft.* forumtopic ft left bring together forumtopicpost ftp on ft.id = ftp.topic_id ft.forum_id = ".$forum->id." grouping ftp.topic_id order greatest(coalesce(ft.created_at, '0000-00-00 00:00:00'), coalesce(ftp.created_at, '0000-00-00 00:00:00') ) desc

what sticks out me group by. have no aggregation functions, , no references ftp. think want this:

select ft.* forumtopic ft left bring together forumtopicpost ftp on ft.id = ftp.topic_id ft.forum_id = ".$forum->id." grouping ft.topic_id order greatest(coalesce(ft.created_at, '0000-00-00 00:00:00'), coalesce(max(ftp.created_at), '0000-00-00 00:00:00') ) desc;

note in add-on max() group by changed. want aggregate id in first table. might null in sec table.

mysql phalcon