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->join('node', 'n', 'n.nid = c.nid');
$query->fields('c', array('cid', 'last_comment_timestamp')); // fields
$query->fields('n', array('nid', 'title'));
$query->condition('n.type', 'article'); // e.g filter for articles only
$query->condition('n.status', 0, '>');
$query->orderBy('c.last_comment_timestamp', 'DESC'); // Order by last commented
$results = $query->execute();
foreach($results as $row) {
drupal_set_message('<pre>'.print_r($row,1).'</pre>'); // debug
}
node_comment_statistics table schema:
mysql> 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 | |
+------------------------+------------------+------+-----+---------+-------+