2

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
0

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) > 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) > 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
  • Your answer has gotten me very close to the correct solution. It does indeed delete the records with duplicate IDs and leaves rows with only the max rate. However, your answer also deletes the other records in the database.I would like to preserve all other entries in the database and delete only the duplicate IDs that were selected in the query. After rereading my OP I realized it wasn't very descriptive. Sorry, I'm a SQL newbie! – SickRiffsAlways Feb 26 '17 at 14:38
  • In You question - it was described as correct form :-) , For this just need remove - having count(distinct Rate) > 1) t – a_vlad Feb 26 '17 at 19:40
  • That did the trick! Thanks for your help. – SickRiffsAlways Feb 27 '17 at 7:35
1

Here is an example of using the ROW_NUMBER windowing function to solve the problem. I added a duplicate row for Redmond to make sure we kept at least one for that ID and Rate.

set nocount on
--Create sample data
Declare @TestTable Table (ID int, Rate Decimal(11,2), City varchar(100))
insert into @TestTable (ID, Rate, City) values
    (4,8.62,'Minneapolis'),
    (4,23.72,'Minneapolis'),
    (4,29.85,'Minneapolis'),
    (6,24.00,'Redmond'),
    (6,28.75,'Redmond'),
    (6,28.75,'Redmond')

--Use Row_Number windowing function to group by ID and order by Rate descending
;
WITH TestTableWindowed
AS (
    SELECT ID
        ,Rate
        ,ROW_NUMBER() OVER (
            PARTITION BY ID ORDER BY Rate DESC
            ) AS RowNumber
    FROM @TestTable
    )

--Delete where RowNumber<>1 for each group
DELETE TestTableWindowed
WHERE RowNumber <> 1

--Select the final results    
SELECT *
FROM @TestTable

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.