Delete records with multiple values for a column I am attempting to clean some data from within the AdventureWorks database on SQL Server 2012. Some of the records have multiple values for the Rate field.

I have already found a query for selecting the rows:

select x.* 
from t as x
where x.ID 
IN (
    select ID
    from t
    group by ID
    having count(Rate) &gt; 1
) 


Here is the simplified output:

ID   Rate   City
4    8.62   Minneapolis
4    23.72  Minneapolis
4    29.85  Minneapolis
6    24.00  Redmond
6    28.75  Redmond


Now, I wish to delete the records with multiple values for the Rate field and keep ONLY the highest Rate for each ID. For example:

ID   Rate   City
4    29.85  Minneapolis
6    28.75  Redmond
not sure about count(distinct Rate) > 1, think distinct is not necessary (but this depends from Your logic and not change query)

WITH tmp AS 
        (select 
                ID,
                MAX(Rate) AS Rate,
                MAX(City) AS City
        from t
        group by ID
        having count(distinct Rate) &gt; 1)


select x.* 
from t as x
LEFT JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate
WHERE tmp.ID IS NOT NULL


must return expected data

LEFT JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate
    WHERE tmp.ID IS NOT NULL


could be changed by INNER JOIN

INNER JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate


add final form as asked (delete all not), edited:
original query was have 

having count(distinct Rate) &gt; 1


as described in question - correct form of expected query, after comment - we need just remove it

WITH tmp AS 
        (select 
                ID,
                MAX(Rate) AS Rate,
                MAX(City) AS City
        from t
        group by ID
        )


DELETE x
from t as x
LEFT JOIN tmp ON x.ID = tmp.ID AND x.Rate = tmp.Rate
WHERE tmp.ID IS NULL