select pairs, ommit single

opposite of distinct

[mcibor]

Hi all!

I have a small problem, which resolving would really improve my life.

I have a table:
id, value
1, ble
2, ble
3, unique
4, not unique
5, not unique

What I need is a query that will return all the ids that are not unique. In this example it will be 1, 2, 4 and 5

Regards
Michal Cibor

[directrix]

This is standard SQL, and works in the latest version of MySQL:

select * from file where value in (select value from file group by value having count(*) > 1)

[Blackie]

I think it is not possible to do in one statement.

P.S.
CREATE TEMPORARY TABLE dupes SELECT * FROM tablename GROUP BY colname HAVING COUNT(*)>1 ORDER BY colname;
SELECT t.* FROM tablename t, dupes d WHERE t.colname = d.colname ORDER BY t.colname;

[mcibor]

Thanks guys!

Blackie, your solution works like a charm! That's what I was looking for.
Directix, I get the error
SELECT id, pass, user, email, ip
FROM players
WHERE pass
IN (
SELECT pass
FROM players
GROUP BY pass
HAVING count( * ) > 1
)

#1064 - You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'select pass from players group by pass having count( * ) > 1 )

CU round!
Michal

[Blackie]

I think IN (and those enclosed statements) work in newer MySQL starting from 5.0