title: How to group parent/child records by flag and collapse to a single record for the combo where the flag is true if one or more expanded was true?
body: <p>Sorry about the long title it was difficult to describe what I am trying to do.  It is simple to understand as you will see below, but difficult to summarise into a title.</p>
  <p>I have a query that returns the following data:</p>
  <pre><code>| ParentID | ChildID | Flag |
  | 100      | 1       | 0    |
  | 100      | 1       | 1    |
  | 100      | 2       | 0    |
  | 100      | 2       | 0    |
  | 200      | 1       | 1    |
  | 200      | 1       | 1    |
  </code></pre>
  <p>The flag column will only have 1 or 0 in it.
  I need to filter the results so that there is only a single row for each parent/child combination.
  The flag should be 1 if there were one or more records in the full result set above where it was 1 for the parent/child combo, otherwise it should be zero.</p>
  <p>So the result if applied to the above would be:</p>
  <pre><code>| ParentID | ChildID | Flag |
  | 100      | 1       | 1    |
  | 100      | 2       | 0    |
  | 200      | 1       | 1    |
  </code></pre>
  <p>I have this working with just the ChildID and Flag columns:</p>
  <pre><code>DECLARE @InMemoryResultsFirstPass AS TABLE (ChildID Integer, Flag Integer)
  DECLARE @InMemoryResultsRecs AS TABLE (ChildID Integer, Flag Integer)
  
  INSERT INTO @InMemoryResultsFirstPass
        SELECT 1 ChildID, 0 Flag
  UNION SELECT 1 ChildID, 1 Flag
  UNION SELECT 2 ChildID, 0 Flag
  UNION SELECT 2 ChildID, 0 Flag
  UNION SELECT 1 ChildID, 1 Flag
  UNION SELECT 1 ChildID, 1 Flag
  
  select * from @InMemoryResultsFirstPass
  
  INSERT INTO @InMemoryResultsRecs
  SELECT DISTINCT 
      result.* 
  FROM @InMemoryResultsFirstPass imr
      CROSS APPLY (
          SELECT TOP 1
              *
          FROM @InMemoryResultsFirstPass imrfp
          WHERE imrfp.ChildID = imr.ChildID
          ORDER BY imrfp.Flag DESC
      ) result
  
  select * from @InMemoryResultsRecs
  </code></pre>
  <p>But have been unable to work out how to do this once I add it in the ParentID column.  I have tried a few different approaches trying to do a nested query in the CROSS APPLY with GROUP BY on the ParentID but no matter what I try I lose the ParentID = 200 records:</p>
  <pre><code>DECLARE @InMemoryResultsFirstPass AS TABLE (ParentID Integer, ChildID Integer, Flag Integer)
  DECLARE @InMemoryResultsRecs AS TABLE (ParentID Integer, ChildID Integer, Flag Integer)
  
  INSERT INTO @InMemoryResultsFirstPass
        SELECT 100 ParentID, 1 ChildID, 0 Flag
  UNION SELECT 100 ParentID, 1 ChildID, 1 Flag
  UNION SELECT 100 ParentID, 2 ChildID, 0 Flag
  UNION SELECT 100 ParentID, 2 ChildID, 0 Flag
  UNION SELECT 200 ParentID, 1 ChildID, 1 Flag
  UNION SELECT 200 ParentID, 1 ChildID, 1 Flag
  
  select * from @InMemoryResultsFirstPass
  
  INSERT INTO @InMemoryResultsRecs
  SELECT DISTINCT 
      result.* 
  FROM @InMemoryResultsFirstPass imr
      CROSS APPLY (
          SELECT TOP 1
              *
          FROM @InMemoryResultsFirstPass imrfp
          WHERE imrfp.ChildID = imr.ChildID
          ORDER BY imrfp.Flag DESC
      ) result
  
  select * from @InMemoryResultsRecs
  </code></pre>
  <p>Any assistance would be greatly appreciated.</p>
  <p>Thank you for your time.</p>