How to delete duplicate rows with filter

Question

Have table like :

declare @tbl TABLE (id int,sub_id int,[money] money)
insert into @tbl (id,sub_id,money)
SELECT 289,33602502,800.00
UNION ALL
SELECT 300,33602502,805.00
UNION ALL
SELECT 735,33603527,7175.77
UNION ALL
SELECT 741,33603527,7172.77
UNION ALL
SELECT 2049,33606066,4817.77
UNION ALL
SELECT 2060,33606066,4791.77
UNION ALL
SELECT 2598,33607099,4084.77
UNION ALL
SELECT 2605,33607099,4053.77

Want delete rows where sub_id are same and money field is min. For example response must looks like :

id   sub_id      money
289 33602502    800.00
741 33603527    7172.77
2060 33606066   4791.77
2605 33607099   4053.77

How to do it ?

Start by working out how you would select the rows you want to delete. And how you uniquely identify them, — Richard, Aug 07 '15 at 08:01
if there are 3 rows with same sub_id, do you want to delete 2 ? Sounds logical, but your text says you only want to delete the lowest — t-clausen.dk, Aug 07 '15 at 08:09

score 1 · Accepted Answer · answered Aug 07 '15 at 08:01

1

Try this :

DELETE FROM @tbl WHERE id in(
select t1.id from @tbl t1
inner join @tbl t2 on t1.sub_id = t2.sub_id
WHERE t1.money > t2.money)

select * from @tbl

answered Aug 07 '15 at 08:01

GeoVIP

1,524
9
25
45

this has a self join, causing the execution plan to be less effective than necessary – t-clausen.dk Aug 07 '15 at 08:42

t-clausen.dk · Answer 2 · 2015-08-07T08:11:49.743

1

You should use OVER Clause for tasks like this. This will delete the row with the lowest money for each sub_id:

;WITH cte as
(
  SELECT *, row_number() over (partition by sub_id order by money) rn from @tbl
)
DELETE cte where rn = 1

edited Aug 07 '15 at 08:11

answered Aug 07 '15 at 08:04

t-clausen.dk

43,517
12
59
92

score 0 · Answer 3 · answered Aug 07 '15 at 08:03

declare @tbl TABLE (id int,sub_id int,[money] money)
insert into @tbl (id,sub_id,money)
SELECT 289,33602502,800.00
UNION ALL
SELECT 300,33602502,805.00
UNION ALL
SELECT 735,33603527,7175.77
UNION ALL
SELECT 741,33603527,7172.77
UNION ALL
SELECT 2049,33606066,4817.77
UNION ALL
SELECT 2060,33606066,4791.77
UNION ALL
SELECT 2598,33607099,4084.77
UNION ALL
SELECT 2605,33607099,4053.77


delete from t from  @tbl t
inner join (    
select sub_id,MIN(money)as money from @tbl
group by sub_id
) p on p.sub_id=t.sub_id and p.money=t.money


select * from @tbl

How to delete duplicate rows with filter

3 Answers3