Find duplicate rows and delete specific one

Question

I got table like this:

Col1 | Col2
AAA  | 1
BBB  | X
AAA  | X
CCC  | 1

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.

I found how to find the duplicates:

SELECT col1, col2, col3=count(*)
INTO holdkey
FROM t1
GROUP BY col1, col2
HAVING count(*) > 1

But this example further shows only how to delete identical rows and not how to choose one.

btw, this is mssql2000

Answer 1

Assuming the table is called test, this should work. I just tried it :-)

DELETE From test WHERE Col1 IN(
SELECT Col1 FROM test 
GROUP BY Col1
HAVING COUNT(1) > 1)
AND Col2 = '1'