1

In MSSQL TABLE I have 3 columns.

ID (IDENTITY), KeptValue(NVARCHAR), SPENT(MONEY).

I have multiple KeptValues that are duplicated. I need to delete all duplicates and keep 1. But I need to keep the one with the greatest spent.
I have 2 views created. a list of the duplicated kept values VW_DUPLICATE1.

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
Julian Michael Mostert
  • 11
  • 1

5 Answers5

2
delete t1
from your_table t1
left join  
(
   select keptvalue, max(spent) as mSpent
   from your_table
   group by keptvalue

) t2 on t1.keptvalue = t2.keptvalue and t1.spent = t2.mSpent
where t2.mSpent is null
juergen d
  • 201,996
  • 37
  • 293
  • 362
  • 2
    id is not mentioned in group by it throws error "Column 'id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause" – bvr Jul 09 '13 at 11:11
  • @bvr: Of course. I missed that. Thanks. – juergen d Jul 09 '13 at 11:22
0

You can use this query to delete by specific KepValue keeping the highest SPENT.

DELETE FROM your_table WHERE SPENT < (SELECT MAX(SPENT) FROM your_table WHERE KeptValue='your_value') AND KeptValue='your_value'
Soham Dasgupta
  • 5,061
  • 24
  • 79
  • 125
0

Try this

DELETE FROM tbl WHERE Id IN (
    SELECT Id FROM (
        SELECT 
            Id
            ,ROW_NUMBER() OVER (PARTITION BY KeptValue ORDER BY SPENT DESC) AS [ItemNumber]
        FROM 
            tbl
    ) a WHERE ItemNumber > 1 
)
Gayathri L
  • 1,407
  • 11
  • 13
0

Try this 

Create table #temp ( ID int, KeptValue NVARCHAR(20) , SPENT numeric(18,3))

INSERT INTO #temp select 1,'KeptValue1', 20
INSERT INTO #temp select 2,'KeptValue1', 21
INSERT INTO #temp select 3,'KeptValue2', 22
INSERT INTO #temp select 4,'KeptValue2', 20
INSERT INTO #temp select 5,'KeptValue2', 20
INSERT INTO #temp select 6,'KeptValue3', 20
INSERT INTO #temp select 7,'KeptValue3', 23
INSERT INTO #temp select 8,'KeptValue3', 24
INSERT INTO #temp select 9,'KeptValue4', 28
INSERT INTO #temp select 10,'KeptValue4', 23
INSERT INTO #temp select 11,'KeptValue5', 24
INSERT INTO #temp select 12,'KeptValue6', 28

select * FROM #temp 

DELETE 
FROM #temp 
WHERE ID 
in(
    select ID from  
                    (SELECT Id, (ROW_NUMBER() OVER(PARTITION BY KeptValue order by SPENT desc)) as R 
                    from #temp
                    ) as RowsNm
    WHERE R >1)

select * from #temp 


drop table #temp
wala rawashdeh
  • 423
  • 5
  • 17
-1

You can achive this by doing a group by KeptValue and a max aggregate on Spent

Alicia
  • 1,152
  • 1
  • 23
  • 41