How do you find the second, of the two duplicates?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do you find the second, of the two duplicates?

Post by simonmlewis »

Code: Select all

SELECT a,b
FROM tbl
GROUP BY a,b
HAVING COUNT(*)>1; 
This script works, but it always shows the FIRST entry to say "this has a duplicate".

How do I find "the duplicate", thus being able to delete all duplicates?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
Weirdan
Moderator
Posts: 5978
Joined: Mon Nov 03, 2003 6:13 pm
Location: Odessa, Ukraine

Re: How do you find the second, of the two duplicates?

Post by Weirdan »

If you want to delete duplicates, you don't have to select them. The most efficient way I know is to just add unique key ignoring any errors (if you want to keep the first record from any set of duplicates):

Code: Select all

alter ignore table tbl add unique index a_b_uniq(a, b);
Or, with more control over the order of insertion:

Code: Select all

create table tbl_new like tbl;
alter table tbl_new add unique index a_b_uniq (a,b);
insert ignore into tbl_new select * from tbl order by id asc;
rename table tbl to tbl_old, tbl_new to tbl;
drop table tbl_old;
-- optionally, drop unique index
alter table tbl drop index a_b_uniq;
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: How do you find the second, of the two duplicates?

Post by requinix »

You can also LEFT JOIN the table against itself on the same data but a different primary key, then keep only the rows where the joined data is null.

Code: Select all

SELECT a, b FROM tbl t1 LEFT JOIN tbl t2 ON t1.a = t2.a AND t1.b = t2.b AND t1.pk != t2.pk WHERE t2.pk IS NULL
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Re: How do you find the second, of the two duplicates?

Post by pickle »

If they're duplicates, why does it matter which one you delete?
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you find the second, of the two duplicates?

Post by simonmlewis »

Because I made the mistake of allowing as many as they want, and want only to keep the first one.
Tho you do have a point; if they don't realise the others have gone, why does it matter.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
Post Reply