title: SQL - Ugly combination of GROUP BY and COALESCE
body: <p>I have a table with data similar to the following:</p>
  
  <h3>[ID], [State], [foo], [DateCreated], [DateUpdated]</h3>
  
  <p>The longer I work on this, the uglier my SQL is getting, which tells me I'm probably doing something wrong.  What I want is a unique list of each State so long as foo is always the same for that State <em>(if foo is not the same for all records in that State, I don't want that State at all)</em>.  Also, I want to COALESCE DateCreated and DateUpdated and want the maximum value for that State.</p>
  
  <p>So given this data:</p>
  
  <pre><code>[ID], [State], [foo], [DateCreated], [DateUpdated]
  1,  MA, data1,  05/29/2012, 06/02/2012
  2,  MA, data1,  05/29/2012, 06/03/2012
  3,  RI, data2,  05/29/2012, NULL
  4,  RI, data3,  05/29/2012, NULL
  5,  NH, data4,  05/29/2012, NULL
  6,  NH, data4,  05/29/2012, 06/05/2012
  </code></pre>
  
  <p>I'd like only these results:</p>
  
  <pre><code>[State], [foo], [LastUpdated]
  MA, data1,  06/03/2012
  NH, data4,  06/05/2012
  </code></pre>
  
  <p>What's the most elegant way to get what I'm after?</p>