find max value per group

Question

first find max part rate from 0001 branch, if not found in 0001 branch then find max rate in all the other branches.

Table
 
Result