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) > 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