title: Find Duplicate Phones using cte by comparing against other groups
body: <p>I am trying to determine how many duplicate work phones I can find in Group 94 by comparing against all other groups that are active.</p>
  
  <ul>
  <li>Contacts are grouped using GroupId </li>
  <li>The ContactTable Only contains ids and NOT Phones </li>
  <li>The DetailedContactTable contains phones </li>
  <li>The ContactSummaryTable contains Group status</li>
  </ul>
  
  <p>The sql is working by setting rownumber for each duplicate Workphone > 1. </p>
  
  <p>The problem is that is setting the rownumber > 1 for all groups except for 94. I need to know 94 first and foremost. Any idea how I set rownumber > 1 for duplicates in GroupId 94 first?</p>
  
  <pre><code>DECLARE @GroupID Int
  SET @GroupID = 94   
  
  
  ;WITH cte AS
  (
    SELECT ROW_NUMBER() OVER (PARTITION BY d.WorkPhone ORDER BY c.id DESC)
      AS rownumber
      ,d.WorkPhone
      ,c.id
      ,GroupID
      FROM ContactTable c
      INNER JOIN DetailedContactTable d
      ON c.DetailedContactId = d.id
      WHERE  c.GroupID IN 
      (
          SELECT id 
              FROM ContactSummaryTable WHERE id = @GroupID
              OR GroupActive = 1
      )
      AND NOT d.WorkPhone IS NULL
      AND d.WorkPhone &lt;&gt; ''
  )
  SELECT * FROM cte WHERE rownumber &gt; 1 
  ORDER BY GroupID;
  </code></pre>