title: Issue with MAX statement on CASE column in MS SQL
body: <p>I'm having trouble getting the second MAX statement in the query below to work as i would like, to give an example of the output I get at the moment;</p>
  
  <pre><code>PART                    FREE_STOCK      BIN             ME_BIN
  01TSC7EM                21.0000         A1E             NULL
  01TSC7EM010MLG           7.0000         A1E             NULL
  01TSC7EM010MME           7.0000         218             218         
  01TSC7EM010MSM           3.0000         407             NULL
  01TSC7EM010MXL           4.0000         407             NULL
  </code></pre>
  
  <p>The first MAX for the <code>BIN</code> column is working as i expect with the grouping meaning that the the short part gets the <code>BIN</code> location with the most <code>FREE_STOCK</code>, I was hoping by using a case statement and following similar logic that I could also return the <code>BIN</code> location for a part ending <code>'%ME'</code> and use the same MAX behaviour to set it for the short part too.</p>
  
  <p>Is this because i'm using a case statement to provide the values rather than doing it against a column? Any suggestions for how I can get this to work?</p>
  
  <pre class="lang-tsql prettyprint-override"><code>SELECT dbo.part_bins.part, SUM(dbo.part_bins.free_stock) AS FREE_STOCK, MAX(PART_BINS.BIN) AS Bin, 
         MAX(CASE WHEN part_bins.part like '%ME' then PART_BINS.BIN END) AS ME_Bin
  FROM   dbo.part_bins 
  WHERE  dbo.part_bins.free_stock &gt; 0 
  GROUP BY dbo.part_bins.part
  UNION
  SELECT part.master_part AS part, SUM(dbo.part_bins.free_stock) AS FREE_STOCK,     MAX(PART_BINS.BIN) AS Bin, 
         MAX(CASE WHEN part.master_part like '%ME' THEN PART_BINS.BIN END) AS ME_Bin
  FROM   dbo.part_bins 
  WHERE  dbo.part_bins.free_stock &gt; 0                   
  GROUP BY part.master_part
  </code></pre>
  
  <p>I'm hoping I can get the final result to be:</p>
  
  <pre><code>PART                    FREE_STOCK      BIN             ME_BIN
  01TSC7EM                21.0000         A1E             218
  01TSC7EM010MLG           7.0000         A1E             NULL
  01TSC7EM010MME           7.0000         218             218         
  01TSC7EM010MSM           3.0000         407             NULL
  01TSC7EM010MXL           4.0000         407             NULL
  </code></pre>