Finding duplicate fields
Kell Gatherer
entries some of which are doubled up.
Can anyone suggest a SELECT query which would find them?
There are many fields, but only two are relevant: 'reference' and 'jpeg':
there should never be two entries with the same reference and jpeg.
Of course, I've done a
"SELECT DISTINCT reference FROM images ORDER BY reference"
and then I can step through all the jpegs in order seeing if there
duplicates, but if there's one thing I've learned about MySQL there's
always a more elegant way....
TIA,
--
Kell Gatherer
ke...@mighty.me.uk
www.mighty.me.uk
sheldonlg
Sure. It seems that you want distinct **pairs** of reference and jpeg.
So, just do "select distinct reference, jpeg from images order by
reference"
Motosauro
I think a :
SELECT DISTINCT reference, count(reference)
FROM images
GROUP BY reference
WHERE count(reference) > 1
ORDER BY count(reference) DESC, reference ASC
Will do the trick ;)
Kell Gatherer
Motosauro <moto...@gmail.com> wrote:
> I think a :
> SELECT DISTINCT reference, count(reference)
> FROM images
> GROUP BY reference
> WHERE count(reference) > 1
> ORDER BY count(reference) DESC, reference ASC
> Will do the trick ;)
Shouldn't that be:
SELECT DISTINCT reference, count(jpeg)
FROM images
GROUP BY reference WHERE count(jpeg) > 1
ORDER BY count(jpeg) DESC, reference ASC
?
Anyway, the above comes back with 1,764 rows: the number of references.
Each reference may have up to 100 jpegs associated, I want to find
duplicate jpegs.
How about:
SELECT reference, jpeg, COUNT(*)
FROM images
GROUP BY reference, jpeg
HAVING COUNT(*) > 1
Mm, maybe not.
Thanks for assistance.
Kell Gatherer
Well, it seems I've got rid of all the dupes, because I have now created a
UNIQUE index on (reference,jpeg) which didn't fail.
I have another table where I suspect there are duplications, but simpler
this time: each 'key' field should be unique. It's a shame you can't have
more than one field which is auto-incrementing, I created a unique 'key'
number each time, but my method let me down :-(
Thanks for help.
toby
> In article <_audnRuuoJuV5RrVnZ2dnUVZ_qrin...@giganews.com>,
> sheldonlg <sheldonlg> wrote:
>
>
>
> > Kell Gatherer wrote:
> > > Due to an error in my coding :-(now fixed) I have a table with 46,000
> > > entries some of which are doubled up.
>
> > > Can anyone suggest a SELECT query which would find them?
>
> > > There are many fields, but only two are relevant: 'reference' and 'jpeg':
> > > there should never be two entries with the same reference and jpeg.
>
> > > Of course, I've done a
> > > "SELECT DISTINCT reference FROM images ORDER BY reference"
> > > and then I can step through all the jpegs in order seeing if there
> > > duplicates, but if there's one thing I've learned about MySQL there's
> > > always a more elegant way....
>
> > > TIA,
>
> > Sure. It seems that you want distinct **pairs** of reference and jpeg.
> > So, just do "select distinct reference, jpeg from images order by
> > reference"
>
> Well, it seems I've got rid of all the dupes, because I have now created a
> UNIQUE index on (reference,jpeg) which didn't fail.
One way of forcibly eliminating the dupes is to add such an index with
the IGNORE modifier.
>
> I have another table where I suspect there are duplications, but simpler
> this time: each 'key' field should be unique. It's a shame you can't have
> more than one field which is auto-incrementing, I created a unique 'key'
> number each time, but my method let me down :-(
There is a working method for this in the manual.
>
> Thanks for help.
>
> --
> Kell Gatherer