title: Select one distinct row based on a case statement applied to a column
body: <p>I'm unable to figure out a sql query (using MS Sql Server). I'm trying to retrieve a single row from a dataset in which an item with one id can have more than one row. The part that is throwing me off is that the correct row should be based on a "hierarchy". I have trying to throw a case statement at the problem.</p>
  
  <p>Some sample data:</p>
  
  <pre><code>Id           Class       Date
  100          Red         2012-12-12
  100          Blue        2012-12-31
  200          Red         2012-10-31
  300          Green       2012-04-04
  300          Blue        2011-09-01
  </code></pre>
  
  <p>I want to return a single row based on the value of Class. </p>
  
  <pre><code>Case When Red Then
      Date 
  Case When Blue Then 
      Date
  Case When Green Then 
      Date 
  Else
      ''
  </code></pre>
  
  <p>My final dataset should look like this:</p>
  
  <pre><code>Id           Class       Date
  100          Red         2012-12-12
  200          Red         2012-10-31
  300          Blue        2011-09-01
  </code></pre>
  
  <p>So, if one of the duplicate rows has a value of Red, use the date from that row first. Then blue, then green.</p>
  
  <p>Been working on this one all day, playing around with subqueries, group bys, havings, case statements, derived tables. I'm quite rusty on my sql skills, as it's been a while.</p>
  
  <p>Any hints on the direction I should take?</p>