title: How to use Oracle CTE for such query?
body: <p>I have a query that looks like following:</p>
  
  <pre><code>SELECT m.Name, (m.Value + NVL(a1.Value1, 0) + NVL(a2.Value2,0) + NVL(a3.Value3,0) "Value"
  FROM m MainTable
  LEFT JOIN Additional1 a1 ON (...)
  LEFT JOIN Additional2 a2 ON (...)
  LEFT JOIN Additional3 a3 ON (...)
  WHERE (conditions on m)
  ORDER BY 1;
  </code></pre>
  
  <p>These query produces <strong>multiple</strong> rows for each <code>Name</code>.</p>
  
  <p>I need to limit it to <strong>one</strong> row for each <code>Name</code> with the following logic:  include the row which Value is the closest to the average Value for a given Name.</p>
  
  <p>Something tells me that <strong>CTE</strong> should allow for a more compact code and hopefully more effective implementation, so I do not need to repeat the virtually same query several times.</p>
  
  <p>Can you please point me into the right direction?</p>