delete duplicates(leaving one if it meets the criteria)

Question

I have a table like this:

t1    t2    t3
1     01    M6
1     02    M6
2     01    M1
2     01    M1
2     02    M1
3     04    M4
3     04    M4
4     01    M1
4     01    M2

And I need to end up with something like this:

t1    t2    t3
1     01    M6
1     02    M6
2     01    M1
2     02    M1
3     04    M4
4     01    M1
4     01    M2

In other words, I need to:
for each t1, delete the rows that repeat themselves (leaving one of them) on t2 and at the same time have the same t3 value. If they repeat on t2 but have different t3, they won't be deleted.

Was testing with something like this but it's not quite there:

WITH cte AS (
    SELECT t1,
        RNum = ROW_NUMBER() OVER (PARTITION BY t2, t1 order by t3)
    FROM tbl
    )
DELETE FROM cte WHERE RNum > 1;

Hi ovufo! Can you clarify why none of the rows where t1 = 3 are retained in your desired dataset? — APH, Jan 29 '19 at 19:45
You are right, one of them should be retained. Edited, Thanks. — ovufo, Jan 29 '19 at 19:48
Why are none of the rows where `t1=2 and t2=01` in the desired results? Your question says "(leaving one of them)". — Tab Alleman, Jan 29 '19 at 19:50
So really, you want to remove all exact duplicates, leaving one of each unique combination? https://stackoverflow.com/questions/6025367/t-sql-deleting-all-duplicate-rows-but-keeping-one — Tab Alleman, Jan 29 '19 at 20:12

score 0 · Answer 1 · edited Jan 29 '19 at 21:27

0

Execute a query like this:

DELETE from tbl WHERE ROWID in (
  SELECT DISTINCT dup.ROWID
  FROM tbl inner join tbl as dup ON tbl.t1 = dup.t1 AND tbl.t2 = dup.t2 AND tbl.t3 = dup.t3 AND dup.ROWID > tbl.ROWID
)

edited Jan 29 '19 at 21:27

Bernhard

4,855
5
39
70

answered Jan 29 '19 at 20:16

Marlin Pierce

9,931
4
30
52

score 0 · Answer 2 · answered Jan 29 '19 at 20:34

0

With a CTE:

with cte as (
  select 
    t1, t2, t3, 
    ROW_NUMBER() OVER (PARTITION BY t1, t2, t3 order by t1, t2, t3) rn from tbl
)
delete from cte
where rn > 1;

See the demo

answered Jan 29 '19 at 20:34

forpas

160,666
10
38
76

Is there need to use CTE? Can't we do it w/o it? – Gaurav Jan 29 '19 at 22:27

score -1 · Answer 3 · answered Jan 29 '19 at 22:25

-1

Please try below. Hopefully it will help.

 delete from a
 from
 (select t1, t2, t3
       ,ROW_NUMBER() over (partition by t1, t2, t3
                      order by t1, t2, t3) RowNumber 
  from tbl) a
  where a.RowNumber > 1

answered Jan 29 '19 at 22:25

Gaurav

623
5
11

delete duplicates(leaving one if it meets the criteria)

3 Answers3