selecting duplicate IDs in mysql

Question

I have a very simple table that has just part_id and part_type:

CREATE TABLE `temp` (
`part_id` INT NOT NULL ,
`part_type` CHAR( 5 ) NOT NULL

This has a long list of part id's and types. However, some parts have more than one type. How would I get the IDs of just the parts that have more than one type? I was thinking something like this would work:

SELECT * FROM temp WHERE part_in IN (SELECT count(part_id) as duplicates FROM temp WHERE 1 GROUP BY part_id) AND duplicates > 1

however, this is clearly just psuedocode and fails. Thanks for your help.

Solution 1

select part_id from temp
group by part_id
having count(*) > 1

This will give you the part_id's with more than one row. You can then wrap that like your example query to get all of the part_id/part_type data for all parts with more than one row.

select * from temp
where part_id in (select part_id from temp
                  group by part_id
                  having count(*) > 1)
order by part_id

Solution 2

I love GROUP_CONCAT for this stuff, because it gives you the answer to this question (how many) and the next question (which types).

select part_type, count(*), group_concat(part_id)
from temp
group by 1
having count(*) > 1;

Comments

[thomas]

in addition: if you are also interested in the count-value (it's nearly for free for the db) you can modify the select to the following: select part_id, count(*) as mycount from temp ...

[ThinkCode]

This gives a different set of data than the OP asked for but yeah, this is an imp query to find duplicates concatenated in one row with one query!

[emmanuel honore]

What is GROUP BY 1?

[Alain Collins]

"group by 1" is a shortcut to the first field, so identical to "group by part_type" in this case.