Find duplicates

[Nicholas Sherlock]

Hey all,

I want to give the user a dialog where they can find pairs of duplicate
entries in the database and resolve these down to a single record. I
have this working fine so far, but I just noticed that it falls down
when there are 3 or more duplicates of a given key. Which is not
surprising, given my query:

SELECT p1.id, p2.id,
FROM participator as p1
LEFT JOIN participator AS p2
ON (p1.key=p2.key AND p1.id<p2.id)
WHERE (p2.key IS NOT NULL)

If duplicates only come in pairs, this'll work fine: The query will
return pairs of distinct duplicate entries. Of course, the lame
p1.id<p2.id trick falls down with 3 or more entries. I want only the ids
from 2 rows of a set of duplicate items, so that the user can fix them
up. What trick am I missing? :)

Cheers,
Nicholas Sherlock

--
[url]http://www.sherlocksoftware.org[/url]

[Captain Paralytic]

Nicholas Sherlock wrote:

> Hey all,
>
> I want to give the user a dialog where they can find pairs of duplicate
> entries in the database and resolve these down to a single record. I
> have this working fine so far, but I just noticed that it falls down
> when there are 3 or more duplicates of a given key. Which is not
> surprising, given my query:
>
> SELECT p1.id, p2.id,
> FROM participator as p1
> LEFT JOIN participator AS p2
> ON (p1.key=p2.key AND p1.id<p2.id)
> WHERE (p2.key IS NOT NULL)
>
> If duplicates only come in pairs, this'll work fine: The query will
> return pairs of distinct duplicate entries. Of course, the lame
> p1.id<p2.id trick falls down with 3 or more entries. I want only the ids
> from 2 rows of a set of duplicate items, so that the user can fix them
> up. What trick am I missing? :)
>
> Cheers,
> Nicholas Sherlock

I'm not totally sure about how you wish to handle greater than 2 dupes
but would:

SELECT p1.id, p2.id,
FROM participator as p1
LEFT JOIN participator AS p2
ON (p1.key=p2.key AND p1.id<p2.id)
WHERE (p2.key IS NOT NULL)
GROUP BY p1.id
HAVING COUNT(*) = 2

work for you?

[Captain Paralytic]

Captain Paralytic wrote:

> Nicholas Sherlock wrote:
>

> > Hey all,
> >
> > I want to give the user a dialog where they can find pairs of duplicate
> > entries in the database and resolve these down to a single record. I
> > have this working fine so far, but I just noticed that it falls down
> > when there are 3 or more duplicates of a given key. Which is not
> > surprising, given my query:
> >
> > SELECT p1.id, p2.id,
> > FROM participator as p1
> > LEFT JOIN participator AS p2
> > ON (p1.key=p2.key AND p1.id<p2.id)
> > WHERE (p2.key IS NOT NULL)
> >
> > If duplicates only come in pairs, this'll work fine: The query will
> > return pairs of distinct duplicate entries. Of course, the lame
> > p1.id<p2.id trick falls down with 3 or more entries. I want only the ids
> > from 2 rows of a set of duplicate items, so that the user can fix them
> > up. What trick am I missing? :)
> >
> > Cheers,
> > Nicholas Sherlock

> I'm not totally sure about how you wish to handle greater than 2 dupes
> but would:
>
> SELECT p1.id, p2.id,
> FROM participator as p1
> LEFT JOIN participator AS p2
> ON (p1.key=p2.key AND p1.id<p2.id)
> WHERE (p2.key IS NOT NULL)
> GROUP BY p1.id
> HAVING COUNT(*) = 2
>
> work for you?

Oops, maybe make that:

SELECT p1.key, p1.id, p2.id,
FROM participator as p1
LEFT JOIN participator AS p2
ON (p1.key=p2.key AND p1.id<p2.id)
WHERE (p2.key IS NOT NULL)
GROUP BY p1.key
HAVING COUNT(*) = 2

[Captain Paralytic]

Nicholas Sherlock wrote:

> Hey all,
>
> I want to give the user a dialog where they can find pairs of duplicate
> entries in the database and resolve these down to a single record. I
> have this working fine so far, but I just noticed that it falls down
> when there are 3 or more duplicates of a given key. Which is not
> surprising, given my query:
>
> SELECT p1.id, p2.id,
> FROM participator as p1
> LEFT JOIN participator AS p2
> ON (p1.key=p2.key AND p1.id<p2.id)
> WHERE (p2.key IS NOT NULL)
>
> If duplicates only come in pairs, this'll work fine: The query will
> return pairs of distinct duplicate entries. Of course, the lame
> p1.id<p2.id trick falls down with 3 or more entries. I want only the ids
> from 2 rows of a set of duplicate items, so that the user can fix them
> up. What trick am I missing? :)
>
> Cheers,
> Nicholas Sherlock

Just noticed that the SQL that you have posted is invalid!

Try this:

SELECT p1.`key` , p1.id, p2.id
FROM ndups AS p1
LEFT JOIN ndups AS p2 ON ( p1.`key` = p2.`key`
AND p1.id < p2.id )
WHERE (
p2.`key` IS NOT NULL
)
GROUP BY p1.`key` , p1.`id`
HAVING COUNT( * ) =1
Or maybe, ignore my