title: Window function to remove specific records from SQL Server dataset
body: <p>I have a SQL Server query that returns a dataset showing a person's ID, the entity that person works for, the locations belonging to the entity, the coordinator assigned to the case and the person's status.</p>
  
  <pre><code>   ID    EntityName     LocationName      AssignedTo     StatusName
     17       F&amp;S            St. Lucie         A Hardon       Active
     17       F&amp;S            St. Lucie         A Hardon       Withdrawn
     18       F&amp;S            NH                A Hardon       Withdrawn
     20       H&amp;H            NCH               B Reedy        Active
  </code></pre>
  
  <p>I need to eliminate records where the only status for the combination of EntityName, LocationName and AssignedTo is Withdrawn.  So in the dataset above I would want to remove ID = 18 with LocationName = NH.</p>
  
  <p>I've tried using a Window Function, which may be on the right path but I'm not sure how to proceed:</p>
  
  <pre><code>   Select id, entityname, locationname, assignedto, statusname
     Into #test
     From Table A
  
     Select *,
            row_number()over(partition by entityname, locationname, assignedto
                             order by case when statusname = 'Withdrawn' then 1
                                      else 2 end) as rn
    from #test
  </code></pre>
  
  <p>This gives me a result like this:</p>
  
  <pre><code>    ID   EntityName   LocationName     AssignedTo     StatusName      RN
      17     F&amp;S          St. Lucie        A Hardon       Withdrawn      1
      17     F&amp;S          St. Lucie        A Hardon       Active         2
      18     F&amp;S           NH              A Hardon       Withdrawn      1
      20     H&amp;H           NCH             B Reedy        Active         1
  </code></pre>
  
  <p>But now I'm stuck on how to continue, or if I'm even going about this the wrong way.</p>