[SOLVED] Grab similars?

[bcoffin]

Suppose my table looks like this:

 

id|firstname|lastname

 

and example rows looks like this:

 

1|adam|anderson

2|adam|allen

3|adam|allen

4|bob|barker

5|bob|barker

6|bob|brown

 

..but there's 100s of 1000s of records...

 

What's a good, fast query to select the rows that have duplicate first and last names?

 

So that only rows with IDs 2,3,4,5 are returned?

 

is there a

[akitchin]

you can use the COUNT() function along with GROUP BY and HAVING:

 

SELECT COUNT(id) AS count, firstname, lastname FROM table GROUP BY CONCAT(firstname, lastname) HAVING count > 1

 

this should, in theory, select the firstname and lastname (along with the count) of any firstname/lastname pair (which is why we're grouping by CONCAT()) having a count greater than 1.  you can then use these names to do whatever you'd like (to get the id's, simply select where firstname and lastname match).

 

this should work in principle - i haven't used CONCAT in a group by clause before.  to find out about these functions, id encourage you to go to the functions and operators section of the mysql manual.

[effigy]

You may be able to use GROUP_CONCAT as well. There's an example here.

[akitchin]

thanks effigy, find a new gem from MySQL everyday (although realistically, i should know it if i studied SQL instead of specifically MySQL on a per-application basis).

[bcoffin]

Thanks to you all.