query to find duplicate values in certain field?

sneakyimp

i'm importing some data...for one particular field, i want to get a list of all values that are duplicated.

in other words for the field called myfield in table mytable, i want to get a list of all values in myfield that occur more than once.

kind of like the inverse of SELECT DISTINCT

Drakla

SELECT t1.id, t1.field FROM mytable as t1, mytable as t2 WHERE t2.id > t1.id AND t1.field = t2.field ORDER BY t1.id;

You have to join the table to itself with aliases, and I'll presume you have some sort of primary key, some people put t1.id != t2.id but the > method makes much more sense if you think about it.

You can also do things with GROUP BY on a field and use HAVING numthings > 1 but just try that for now.

mrhappiness

SELECT myfield, COUNT(myfield) occurrences
FROM mytable
GROUP BY myfield
WHERE COUNT(myfield) > 1

something like that?

Drakla

Yup! Definitely something like that, except in the world of MySQL in case that's what you're using, which seems to have a problem with not using aliases (the only thing apart from crosstabs I found useful when doing Jet SQL) so in that case you'd have to use

SELECT country, COUNT(country) occurrences
FROM boof
GROUP BY country
HAVING occurrences > 1

sneakyimp

right on!

i am using mySQL. i had tried the self-join approach a bit but it was listing everything twice.

this one seems to work really well:

SELECT category_id, COUNT( category_id ) occurrences
FROM myplan_majors_categories_assoc
GROUP BY category_id
HAVING occurrences >1