Select rows where column contains same data in more than one record

Question

There are plenty of questions with similar titles, but I haven't been able to find an answer that doesn't involve group by (GROUP BY x HAVING COUNT(*) > 1), but what I'm looking for is a query that returns all rows ungrouped (in MySQL).

Say I have the following:

id  data
1    x
2    y
3    y
4    z

What I want the query to return is:

2    y
3    y

based on the fact that rows 2 and 3 have identical values in the data column.

SELECT * FROM table WHERE [data contains a value that exists in some other row as well]

Answer 1

You have to put it in a subquery

select * from table where data in (
    select data from table group by data having count(*) > 1
)

• see it working live in an sqlfiddle