...

View Full Version : how to show 'all' duplicates in a single field



needsomehelp
01-07-2012, 01:24 PM
I have got this far and would like to find out how I change the query so it shows all duplicates instead of just one of them.
SELECT count(*), `id`, `linklocation`, `link`
FROM `links`
WHERE `link` != ''
GROUP BY `link` having count(*) > 1

sunfighter
01-07-2012, 05:04 PM
Needsome I am learning mysql also. Most of the time I ear mark a question and wait for Old Pendent to reply, but every once in a while I give it a shot.
Try this and if it don't work we'll both wait for the master, OK?

SELECT *
FROM links
WHERE link IN (
SELECT link
FROM links
GROUP BY link
HAVING count(*) > 1
)
ORDER BY link

needsomehelp
01-07-2012, 09:23 PM
Thank you sunfighter,

I tried


SELECT *
FROM `links`
WHERE `link` IN (
SELECT `link`
FROM `links`
GROUP BY `link`
HAVING count(*) > 1
)
ORDER BY `id` DESC


But it caused the server error 500, so I entered it in phpmyadmin and got no results.

sunfighter
01-07-2012, 11:05 PM
OK, We wait then.

Old Pedant
01-08-2012, 08:51 PM
Although MySQL sloppily allows it, it is actually an error to use GROUP BY for other than *ALL* the non-aggregate fields in your SELECT.

SO:


SELECT count(*), id, linklocation, link
FROM links
WHERE link != ''
GROUP BY id, linklocation, link
HAVING count(*) > 1

Doing otherwise just cause MySQL to hide the truth from you.



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum