title: Complex Query To Group and (Potentially Union) Data
body: <p>Suppose I have a SQL Server table like this:</p>
  
  <pre><code>Event |  Item1   |  Item2  | Status
  -------------------------------------------
  1        Parent1   Child1      1
  2        Parent1   Child2      1
  3        Parent2   Child1      1
  4        Parent2   Child2      1
  5        Parent3   Child1      1
  6        Parent3   Child2      0
  7        Parent4   Child1      1
  8        Parent4   Child1      1
  9        Parent4   Child2      0
  </code></pre>
  
  <p>I want a query that gives me the following:</p>
  
  <ol>
  <li>All rows with a status of 0. => That would be IDs 6 and 9. </li>
  <li>All rows that have Item2 = 'Child1', that have a status of 1, that have the
  same Item1 as the result in #1. => That would be IDs 5,7 and 8.</li>
  <li>There can be multiple rows that match #2 and the matches should be
  constrained to maximum id based per Item 1.</li>
  </ol>
  
  <p>So the final result should look like this:</p>
  
  <pre><code>6, Parent3, Child2, 0
  9, Parent4, Child2, 0
  5, Parent3, Child1, 1
  8, Parent4, Child1, 1
  </code></pre>
  
  <p>This solutions is in SQL 2008 R2.
  I'm open to temp tables, unions, joins, etc.  I can't change the schema.  Any help is extremely appreciated.</p>