title: Find duplicate rows and delete specific one
body: <p>I got table like this:</p>
  
  <pre><code>Col1 | Col2
  AAA  | 1
  BBB  | X
  AAA  | X
  CCC  | 1
  </code></pre>
  
  <p>I want to find duplicates based on Col1. Then i want to leave the row which has 'X' in Col2 and delete the other one.</p>
  
  <p>I found how to find the duplicates:</p>
  
  <pre><code>SELECT col1, col2, col3=count(*)
  INTO holdkey
  FROM t1
  GROUP BY col1, col2
  HAVING count(*) &gt; 1
  </code></pre>
  
  <p>But this example further shows only how to delete identical rows and not how to choose one.</p>
  
  <p>btw, this is mssql2000</p>