title: SELECT Row Values WHERE MAX() is Column Value In GROUP BY Query
body: <p>How can I select like this? Can I create a User defined Aggregate Function</p>
  
  <p><code>SELECT Max(A),(SELECT TOP 1 FROM TheGroup Where B=Max(A))</code> FROM MyTable</p>
  
  <p>where MyTable as Shown Below</p>
  
  <pre><code>    A  B  C
  --------------
      1  2  S
      3  4  S
      4  5  T
      6  7  T
  </code></pre>
  
  <p>I want a Query Like this</p>
  
  <pre><code>SELECT MAX(A),(B Where A=Max(A)),C FROM MYTable GROUP BY C
  </code></pre>
  
  <p>I'm Expecting the result as below</p>
  
  <pre><code> MAX(A)  Condition    C
  -----------------------
     3        4         S
     6        7         T
  </code></pre>