I have a query that looks like following:

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;

These query produces multiple rows for each Name.

I need to limit it to one row for each Name with the following logic: include the row which Value is the closest to the average Value for a given Name.

Something tells me that CTE 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.

Can you please point me into the right direction?