Delete duplicate rows based on a condition

Question

I have a table that has ID and EventDate. It has duplicate rows as I used Union of two tables. Now I got to have the rows with the minimum Eventdate and remove the other duplicates.

the table for eg

ID | Date
--- | ---
1 | 10/27/1993
1 | 10/27/1994
2 | 10/17/1993
2 | 08/15/1993

Delete duplicate rows based on condition

By the way what kind of database engine you use (MySQL, Firebird, etc)? — venoel, Jul 05 '17 at 14:30
By the way https://stackoverflow.com/questions/18932/how-can-i-remove-duplicate-rows?rq=1 — venoel, Jul 05 '17 at 14:42

Lamak · Answer 1 · 2017-07-05T14:14:58.673

2

You can use ROW_NUMBER:

;WITH CTE AS
(
   SELECT *,
          RN = ROW_NUMBER() OVER(PARTITION BY ID ORDER BY EventDate)
   FROM dbo.YourTable
)
DELETE FROM CTE
WHERE RN > 1;

edited Jul 05 '17 at 14:14

answered Jul 05 '17 at 14:12

Lamak

69,480
12
108
116

1

CTE lacks `from` – JohnHC Jul 05 '17 at 14:14
@JohnHC what are you talking about? ;). Thanks for the heads up – Lamak Jul 05 '17 at 14:15

Fabiano Carvalho · Answer 2 · 2017-07-05T14:22:52.647

0

Use this!

    delete A
    (
       SELECT *,
              RN = ROW_NUMBER() OVER(PARTITION BY [COLUMN] ORDER BY EventDate ASC),*
       FROM dbo.Your_Table
    ) AS A
where rn > 1

edited Jul 05 '17 at 14:22

answered Jul 05 '17 at 14:18

Fabiano Carvalho

504
1
6
17

How does the row number help? – vba Jul 05 '17 at 14:19
this deletes the whole table – Lamak Jul 05 '17 at 14:20
I do not want to delete based on Row number @FabianoCarvalho – vba Jul 05 '17 at 14:27

venoel · Answer 3 · 2017-07-05T14:38:34.887

0

As documentation (if we about MySQL) you cannot "delete from a table and select from the same table in a subquery".

So

CREATE table1 LIKE table2;
INSERT table2 SELECT * FROM table1; 
DELETE FROM table1
WHERE EXISTS(
  SELECT t2.id FROM table2 t2 WHERE table1.EventDate>t2.EventDate
);
DROP TABLE table2;

Where table1 you original table.

edited Jul 05 '17 at 14:38

answered Jul 05 '17 at 14:29

venoel

463
3
13

score 0 · Answer 4 · answered Jul 05 '17 at 14:37

0

If we talk about Firebird it is enough

DELETE FROM table1 t1_1
WHERE EXISTS(
  SELECT t1_2.id FROM table1 t1_2 WHERE t1_1.EventDate>t1_2.EventDate
);

answered Jul 05 '17 at 14:37

venoel

463
3
13

Delete duplicate rows based on a condition

4 Answers4