Delete duplicates, keeping one of them, grouping by [duplicate]

Question

I need to delete all the duplicates, all but one, for each of the table ids. Like the following. I need to delete all the duplicates on valueid for 01,02,03...

Original:

id | valueid | data ____________________________ 01 | 1001 | datadata1 01 | 1002 | datadata2 01 | 1001 | datadata1 02 | 1323 | datamoredata123 02 | 1323 | datamoredata123 03 | 22123 | evenmoredata 03 | 24444 | andalsomore

Should end like:

id | valueid | data ____________________________ 01 | 1001 | datadata1 01 | 1002 | datadata2 02 | 1323 | datamoredata123 03 | 22123 | evenmoredata 03 | 24444 | andalsomore

Was trying to do it with something like this, but I don´t get how can I group that delete on the id

WITH CTE AS( SELECT valueid, RN = ROW_NUMBER()OVER(PARTITION BY valueid ORDER BY valueid) FROM tblvalues ) DELETE FROM CTE WHERE RN > 1

Any suggestions?

Thanks in advance

Giorgos Betsos · Accepted Answer · 2019-01-07 16:44:13Z

You need to add id column to the PARTITION:

WITH CTE AS( SELECT valueid, RN = ROW_NUMBER()OVER( PARTITION BY id, valueid ORDER BY data) FROM tblvalues ) DELETE FROM CTE WHERE RN > 1

This way you delete duplicate valueid values separately for each id. Column data determines which duplicates are deleted.

Gordon Linoff · Accepted Answer · 2019-01-07 16:46:52Z

You are pretty close. You need to change the partition by clause. You want one row per id/valueid pair, so these should both be in the partitioning clause:

WITH todelete AS ( SELECT valueid, RN = ROW_NUMBER() OVER (PARTITION BY id, valueid ORDER BY data) FROM tblvalues ) DELETE FROM todelete WHERE RN > 1;

Additionally I would include the data column in the order by (after the valueid)...

Fill · Accepted Answer · 2019-01-07 17:16:52Z

-1

A very simple way to do this is to add the UNIQUE index in column (valueid). When you write an ALTER statement, specify the IGNORE keyword.

ALTER IGNORE TABLE tblvalues ADD UNIQUE INDEX idx_name (valueid);

This will remove all duplicate rows. As an added advantage, future INSERTs that are duplicates will be erroneous. As always, you can take a backup before running something like this.

edited Jan 7, 2019 at 17:16

answered Jan 7, 2019 at 16:46

Fill

981 silver badge12 bronze badges

4 Comments

Thom A Over a year ago

When posting a code answer, it's helpful to the OP to explain the the answer you've provided. In this case, how does this answer help the OP DELETE the duplicated rows?

Thom A Over a year ago

Thanks for the update, however, if you run that you'll get the error Unknown object type 'IGNORE' used in a CREATE, DROP, or ALTER statement. . db<>fiddle

Fill Over a year ago

@Larnu update answer, sorry

vvic Over a year ago

The thing is, this is a already closed database design. Can´t really alter it right now. Thanks for your time and answer, though.

Collectives™ on Stack Overflow

Delete duplicates, keeping one of them, grouping by [duplicate]

3 Answers 3

Comments

1 Comment

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

4 Comments

Linked

Related