title: How to find distinct root-nodes of newest nodes in trees (hold in closure table)?
body: <p>I try to store tree of messages as closure table on MySQL. Mostly learned about this method from Bill Karwin's presentation <a href="http://www.slideshare.net/billkarwin/models-for-hierarchical-data" rel="nofollow">Models for hierarchical data</a>. Problem is: i want to find distinct 3 root nodes (=nodes without ancestors), which have most recent nodes in their trees. NB! Even if some root-node has 10 newest node in it's subtree, it would count just once. </p>
  
  <p>All nodes have their modification time, for simplicity lets say that node ids are representing also time of their last modification (but we can't use ids in querys as time), first is 1st and last is 17th. </p>
  
  <pre><code>1
   2
    8
     15
     16
    17
   7
  3
   4
    5
   6
  9
   12
  10
   14
  11
   13
  </code></pre>
  
  <p>In closure table i have 3 columns (ancestor, descendant, depth), so tree is presented like that:</p>
  
  <pre><code>| ancestor | descendant | depth |
  +----------+------------+-------+
  |        1 |          1 |     0 | 
  |        1 |          2 |     1 | 
  |        1 |          7 |     1 | 
  |        1 |          8 |     2 | 
  |        1 |         15 |     3 | 
  |        1 |         16 |     3 | 
  |        1 |         17 |     2 | 
  |        2 |          2 |     0 | 
  |        2 |          8 |     1 | 
  |        2 |         15 |     2 | 
  |        2 |         16 |     2 | 
  |        2 |         17 |     1 | 
  |        3 |          3 |     0 | 
  |        3 |          4 |     1 | 
  |        3 |          5 |     2 | 
  |        3 |          6 |     1 | 
  |        4 |          4 |     0 | 
  |        4 |          5 |     1 | 
  |        5 |          5 |     0 | 
  |        6 |          6 |     0 | 
  |        7 |          7 |     0 | 
  |        8 |          8 |     0 | 
  |        8 |         15 |     1 | 
  |        8 |         16 |     1 | 
  |        9 |          9 |     0 | 
  |        9 |         12 |     1 | 
  |       10 |         10 |     0 | 
  |       10 |         14 |     1 | 
  |       11 |         11 |     0 | 
  |       11 |         13 |     1 | 
  |       12 |         12 |     0 | 
  |       13 |         13 |     0 | 
  |       14 |         14 |     0 | 
  |       15 |         15 |     0 | 
  |       16 |         16 |     0 | 
  |       17 |         17 |     0 | 
  </code></pre>
  
  <p>I could get newest subtrees like that:</p>
  
  <pre><code>SELECT c.ancestor, MAX(time) AS t 
  FROM closure c 
      JOIN nodes n ON (c.descendant = n.node AND c.ancestor &lt;&gt; n.node) 
  GROUP BY c.ancestor ORDER BY t desc;
  </code></pre>
  
  <p>But how could i get distinct 3 <strong>root</strong> nodes having newest postings (1, 10 and 11 in this case)? Is this possible (and rational) with one query?</p>
  
  <hr>
  
  <p>Edit: i put <a href="http://pastebin.com/xMdkRUt9" rel="nofollow">sample tables to pastebin</a></p>