title: How to max(date) and use the in feature in sql server in one query?
body: <p>I have a table such as this</p>
  
  <pre><code>id   color  shade   date
  ---  ----   -----   -----
  1    red    dark    01/01/1990
  2    red    light   09/16/2013
  3    green  light   08/15/2010
  4    green  dark    09/18/2012
  5    maroon dark    08/20/2013
  6    white  dark    08/31/2013
  7    white  light   08/30/2012
  8    purple light   08/20/2010
  </code></pre>
  
  <p>I wanted entries for each color with the latest date. So I tried doing:</p>
  
  <pre><code>select id, color, shade, max(date) from mytable;
  </code></pre>
  
  <p>This didn't work and gave me error: </p>
  
  <pre><code>is invalid in the select list because it is not contained in either an aggregate 
  function or the GROUP BY clause.
  </code></pre>
  
  <p>Alright, so I added it in the <code>Group By</code> as suggested by the error</p>
  
  <pre><code>select id, color, shade, max(date) from mutable
  Group by id, color, shade
  </code></pre>
  
  <p>This gave me the desired results. </p>
  
  <p><strong>Question</strong></p>
  
  <p>Now, I wanted the same query as above repeated only for certain colors. For example I only wanted to see red and green ones. </p>
  
  <p>So I did:</p>
  
  <pre><code>select id, color, shade, max(date) from mutable
  Where color in ('red', 'green')
  Group by id, color, shade;
  </code></pre>
  
  <p>However, this does not give me the correct number of results because I guess they are being grouped by <code>shade</code>. </p>
  
  <p>What is the best way to fetch my desired results? Basically I want <code>max date</code> out of the two instances of same thing and further out of the table only want certain colors. </p>