TSQL Remove duplicated rows on some column only [duplicate]

Question

Possible Duplicate:
SQL - How can I remove duplicate rows?

Example this is my table:

SiteKey, Name, City

The SiteKey is auto increament, Name is different , but some times 2 Sitekey will have same City.

Example:

1, A , CityA
2, B,  CityB
3, C,  CityA
4, D,  CityF

so I need to remove rows 3, and keep rows 1,2,4 only.

its on SQL 2005 and up.

thanks your help.

Answer 1

Here are 2 ways you can do it.

DELETE t
FROM 
<table> t
WHERE EXISTS 
    (SELECT 1 FROM <table> 
    WHERE t.SiteKey > SiteKey AND t.City = City)


DELETE t
FROM 
<table> t
INNER JOIN <table> t2
ON  t.City = t2.City
AND t.SiteKey > SiteKey

Answer 2

This is standard SQL

DELETE
   mytable
WHERE
    SiteKey NOT IN 
    (
    SELECT
       MIN(SiteKey)
    FROM
       Mytable
    GROUP BY
       City
    ) --Don't need 'KeepMe'

Then, add a unique constraint

ALTER TABLE MyTable WITH CHECK ADD
   CONSTRAINT UQ_MyTable_City UNIQUE (City)