Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Show all rows in MySQL that contain the same value

Tags:

php

mysql

I have a MySQL database:

ID | Name
1  | Bob
2  | James
3  | Jack
4  | Bob
5  | James

How would I return a list of all the columns where the same name appears more than once, eg, I'd like to return this:

1  | Bob
2  | James
4  | Bob
5  | James

I've written a count query:

SELECT Name, COUNT(Name) 
AS NumOccurrences 
FROM table 
GROUP BY Name 
HAVING ( COUNT(Name) > 1 )

But that just returns something like this:

Bob   | 2
James | 2

Whereas I want to return the full rows returned.

Any help would be greatly appreciated, thanks.

like image 435
Simon Avatar asked Sep 26 '10 12:09

Simon


People also ask

How do I check if two rows have the same value in MySQL?

Finding Duplicates in MySQLUse the GROUP BY function to identify all identical entries in one column. Follow up with a COUNT() HAVING function to list all groups with more than one entry.

How do I find matching records in MySQL?

MySQL LIKE with Percentage % Wildcard: >> SELECT TeachName, subject FROM data. teacher WHERE subject LIKE 'C%'; Use of the percentage sign before the pattern means that the pattern will match the last location of a value.

How do I count the same values in a column in MySQL?

Find duplicate values in one column First, use the GROUP BY clause to group all rows by the target column, which is the column that you want to check duplicate. Then, use the COUNT() function in the HAVING clause to check if any group have more than 1 element. These groups are duplicate.


1 Answers

You can do it with a sub select

SELECT * FROM table WHERE Name IN (
    SELECT Name FROM table GROUP BY Name HAVING count(*) > 1
)

Also if your distinction match is multiple columns, you can use cartesian sets:

SELECT * FROM table WHERE (firstName, lastName) IN (
    SELECT firstName, lastName FROM table GROUP BY firstName, lastName HAVING count(*) > 1
)
like image 111
MightyE Avatar answered Sep 29 '22 15:09

MightyE