title: SQL Server query select 1 from each sub-group
body: <p>I have a set of data and need to pull out one record for each CON / OWNER / METHOD / MATRIX set.  If there is a non-null RESULT, I want that one.  Otherwise, I want the one with the highest COUNT.  How do I query this?</p>
  
  <pre><code>CON      OWNER      METHOD      MATRIX  RESULT  COUNT
  *CON_1   OWNER_1    METHOD_A    SOLID   NULL    503
  CON_1    OWNER_1    METHOD_A    SOLID   NULL    1
  
  *CON_1   OWNER_1    METHOD_A    SOIL    NULL    1305
  CON_1    OWNER_1    METHOD_A    SOIL    NULL    699
  
  *CON_2   OWNER_2    METHOD_B    SOLID   290     687
  CON_2    OWNER_2    METHOD_B    SOLID   NULL    NULL
  CON_2    OWNER_2    METHOD_B    SOLID   450     600
  
  CON_2    OWNER_2    METHOD_B    WATER   NULL    1
  *CON_2   OWNER_2    METHOD_B    WATER   400     NULL
  </code></pre>
  
  <p>for a result, I would like just the starred records, and I'm showing how each set is grouped.</p>
  
  <p>This is bad SQL:</p>
  
  <pre><code>select top (1) CON, OWNER, METHOD, MATRIX, RESULT, COUNT
  from #TempTable
  group by CON, OWNER, METHOD, MATRIX
  order by CON, OWNER, METHOD, MATRIX, COUNT
  </code></pre>
  
  <p>...because my count isn't part of the aggregate function.  Nor does it deal with the RESULT being NULL or not, and top (1) won't return 1 from each grouping.  However, I've not got farther by using a more complex query (such as based on the question at <a href="https://stackoverflow.com/questions/583954/how-can-i-select-multiple-columns-from-a-subquery-in-sql-server-that-should-hav">How can I select multiple columns from a subquery (in SQL Server) that should have one record (select top 1) for each record in the main query?</a>)</p>
  
  <p>How do I select one from each grouping?</p>