DB API: How to get a list of nodes ordered by highest comment creation date?
The best place to query for that is the node_comment_statistics table.

It contains the relevant comment statistics such as comment count and last comment timestamp for a node.

e.g: Query for article nodes ordered by last_comment_timestamp DESC

$query = db_select('node_comment_statistics', 'c');
$query-&gt;join('node', 'n', 'n.nid = c.nid');
$query-&gt;fields('c', array('cid', 'last_comment_timestamp')); // fields
$query-&gt;fields('n', array('nid', 'title'));
$query-&gt;condition('n.type', 'article'); // e.g filter for articles only
$query-&gt;condition('n.status', 0, '&gt;');
$query-&gt;orderBy('c.last_comment_timestamp', 'DESC'); // Order by last commented
$results = $query-&gt;execute();

foreach($results as $row) {
  drupal_set_message('&lt;pre&gt;'.print_r($row,1).'&lt;/pre&gt;'); // debug
}


node_comment_statistics table schema:

mysql&gt; desc node_comment_statistics;
+------------------------+------------------+------+-----+---------+-------+
| Field                  | Type             | Null | Key | Default | Extra |
+------------------------+------------------+------+-----+---------+-------+
| nid                    | int(10) unsigned | NO   | PRI | 0       |       |
| cid                    | int(11)          | NO   |     | 0       |       |
| last_comment_timestamp | int(11)          | NO   | MUL | 0       |       |
| last_comment_name      | varchar(60)      | YES  |     | NULL    |       |
| last_comment_uid       | int(11)          | NO   | MUL | 0       |       |
| comment_count          | int(10) unsigned | NO   | MUL | 0       |       |
+------------------------+------------------+------+-----+---------+-------+