title: Finding depth of a multi-parent hierarchy - SQL
body: <p>I have a table with two columns: mother - node
  This table forms the base of a hierarchy. A mother refers to a node.
  Every node can have multiple mothers, and every mother can have multiple childs. This is accomplished by multiple rows.
  If mother = NULL, then the node is a top level node. There can be several top-level nodes, and a node can be both a top-level node AND a child of another node.
  e.g:</p>
  
  <pre><code>INSERT INTO MYTABLE VALUES(NULL, 2)
  INSERT INTO MYTABLE VALUES(1, 2)
  </code></pre>
  
  <p>Im now building a procedure that will need to know the maximum depth of the hierarchy. Lets say node E is a child of node D who is a child of node C.
  Node C is a top level node, and also a child of node B who is a child of node A
  Node A is only a top level node.
  If we say node A has a depth = 0. Then in this case the depth of node E should be 4.</p>
  
  <p>Does anyone have a clue to how i could build a statement that would find this depth for me?
  It would have to find the maximum depth of every node in the table and then return the max value of those.</p>
  
  <p>Thanks!</p>
  
  <p>Using SQL Server 2008 btw.</p>
  
  <p>EDIT:</p>
  
  <p>It is ONLY the absolute maximum depth of the table that is of interest. Not the depth of individual nodes. </p>