delete duplicate tuples except for one specific tuple

Question

I want to delete the duplicate(same from and same to values) tuples from a table but need to keep the tuple with minimum object id(object id is pk).

So here are columns:

from | to | time | distance | object_id

I can see the correct number of tuples that will be deleted by executing

select [from],[to],count(*)
FROM table
where [object_id] NOT IN(
    SELECT min([object_id])
      FROM table
      group by [from],[to]
      having count(*) > 1)
group by [from],[to]
having count(*) > 1

but I want to first see the object_id's which are counted on the SQL above.

possible duplicate of [How can I remove duplicate rows?](http://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows) — Martin Smith, Jun 14 '13 at 12:48
To see the rows that will be deleted `;WITH T AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY [from],[to] ORDER BY object_id) AS RN FROM YourTable) SELECT * FROM T WHERE RN > 1` and to actually delete them `;WITH T AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY [from],[to] ORDER BY object_id) AS RN FROM YourTable) DELETE FROM T WHERE RN > 1` — Martin Smith, Jun 14 '13 at 12:52
Once you're done, make sure to add a unique constraint across `from` and `to` so that you only have to run this cleanup once. — Damien_The_Unbeliever, Jun 14 '13 at 12:54
I am not able to see the rows. I give `Invalid column name 'RN'.` — adaminasabi, Jun 14 '13 at 13:05
The code I posted does not give that error. [SQL Fiddle](http://sqlfiddle.com/#!6/610ad/1) — Martin Smith, Jun 14 '13 at 13:17

score 1 · Answer 1 · edited Jan 15 '16 at 03:40

You could try with this (untested)...

;WITH temp AS (
    SELECT [from_id], [to_id], [object_id] = min([object_id])
    FROM table
    group by [from_id],[to_id]
    having count(*) > 1)
SELECT
    t2.[from_id],
    t2.[to_id],
    t.[object_id]
FROM 
    table t
    join temp t2 
        on t2.[from_id] = t.[from_id]
        AND t2.[to_id] = t.[to_id]
        AND t2.[object_id] != t.[object_id]

EDIT:

CTE temp will yield all distinct from/to groupings with min object_id, one you would like to keep.

SELECT [from_id], [to_id], [object_id] = min([object_id])
FROM table
group by [from_id],[to_id]
having count(*) > 1

There are other pairs you would like to remove, and these are same from/to pairs, but with different object_id. Last select should output those records exactly.

Note: I had a bug displaying object_id to keep (from temp) instead to delete (from join with table). Now it's updated. — OzrenTkalcecKrznaric, Jun 14 '13 at 13:05

delete duplicate tuples except for one specific tuple

1 Answers1