title: SQL highest level parent return
body: <p>I have a table that has multi level AssetID with parent/child structure. I want to select the highest level parent asset
  for simplicity sake it's like this:</p>
  <pre>
  AssetID      ParentAssetID
  
  City1        0
  
  City2        0
  
  City3        0
  
  Suburb11     City1
  
  Suburb12     City1
  
  Suburb13     City1
  
  Suburb21     City2
  
  Suburb22     City2
  
  Suburb23     City2
  
  Suburb31     City3
  
  Suburb32     City3
  
  Suburb33     City3
  
  Street11     Suburb11
  
  Street12     Suburb12
  
  Street13     Suburb13
  
  Street21     Suburb21
  
  Street22     Suburb22
  
  Street23     Suburb23
  
  Street31     Suburb31
  
  Street32     Suburb32
  
  Street33     Suburb33
  </pre>
  <p>and a second table like this:</p>
  <pre>
  AssetID    Response
  
  Street31   1
  
  suburb21   2
  
  City2      3
  
  Street12   4
  </pre>
  <p>What I would like to do is select the highest level of parent, in this case city, for each of the responses. for example response 1 will be city1, response 2 will be city 2 etc.</p>
  <p>Or something like this</p>
  <pre>
  City    Response
  
  City3   1
  
  City2   2
  
  City2   3
  
  city1   4
  </pre>