How to get a list of all Groups sorted according to latest comment created on any of the Group Content belonging to that Group? I wrote the following custom query to get a List of Groups sorted according to latest comment created on any of the Group Content belonging to that Group (not on the group itself) .

However this query does not return the list of Groups for which there is no Group Content OR any comment on any of Group Content is created.

SELECT DISTINCT   group_view.nid AS group_nid,
                  group_view.created_date AS group_created_date,
                  discussion.nid AS discussion_nid,
                  comment.created AS comment_created_date,
                  comment.created_date AS comment_created_date
  FROM `node` AS group_view
      LEFT JOIN `og_membership` AS group_map
          ON group_view.nid = group_map.gid
      LEFT JOIN `node` AS discussion
          ON group_map.etid = discussion.nid
             AND group_map.entity_type = 'node'
      LEFT JOIN `comment` AS comment
          ON discussion.nid = comment.nid
  WHERE group_view.type = 'group'
  ORDER BY comment.created DESC


What changes do I need to make in this query to include a list of Groups with no comments

I do not want to use views.
I have not tested a query as I don't have data to test.But I hope the following query will work for you.I am matching comment created date with fetched latest comment by using sub query.

SELECT DISTINCT   group_view.nid AS group_nid,
                      group_view.created_date AS group_created_date,
                      discussion.nid AS discussion_nid,
                      comment.created AS comment_created_date,
                      comment.created_date AS comment_created_date
      FROM `node` AS group_view
          LEFT JOIN `og_membership` AS group_map
              ON group_view.nid = group_map.gid
          LEFT JOIN `node` AS discussion
              ON group_map.etid = discussion.nid
                 AND group_map.entity_type = 'node'
          LEFT JOIN `comment` AS comment
              ON discussion.nid = comment.nid
      WHERE group_view.type = 'group' and (comment.created=SELECT max(c.created) AS latest
      FROM comment c
      WHERE  (c.nid = node.nid)
      GROUP BY c.cid)