title: SQL Server - How to aggregate and retain the "top" value
body: <p>I am using SQL Server 2012.  I have a situation where I am trying to aggregate customers by their "top" account status.  So on the Accounts table, I have the customerkey and then their status per each accounts. Account Status can be opened, closed or retained.  A customer may have several accounts so he/she could be opened and closed and retained.  In this event, we have a hierarchy:</p>
  
  <ol>
  <li>Retained</li>
  <li>Opened</li>
  <li>Closed </li>
  </ol>
  
  <p>So if a customer has ANY account retained, they are retained.  If they have any account opened (and none retained) they are Opened.  If they have any Closed (and none retained or opened) they are closed.  </p>
  
  <p>Eg</p>
  
  <pre><code>CustomerKey Account Number  Status
  A11         1234    Retained
  A11         1235    Closed
  A21         1236    Closed
  A34         1223    Retained
  A34         1566    Opened
  A54         1677    Opened
  </code></pre>
  
  <p>Desired output is </p>
  
  <pre><code>CustomerKey   Status
  A11           Retained
  A21           Closed
  A34           Retained
  A54           Opened
  </code></pre>
  
  <p>Thanks for any pointers!</p>