search for duplicates

I have a table w/ id, first name, last name, score.

I want to do a search of all people who have are in the table more than once.

ie.

select * from table_1 where (first_name+last_name comes up more than twice.)

thanks in advance,

Tony

Two ways to do it...Join the table with itself... just make sure you exclude the rows that match IDs (because they're the same row).


SELECT t1.ID, t2.ID
FROM table1 as t1
JOIN table1 as t2
ON t1.first_name = t2.first_name
AND t1.last_name = t2.last_name
WHERE t1.ID <> t2.ID


Or, do a GROUP BY last_name, first_name HAVING count(*) > 1 (You won't be able to select the IDs this way).


SELECT last_name, first_name, count(*)
ORDER BY 3 desc
GROUP BY last_name, first_name
HAVING count(*) > 1

thank you !!:thumbsup: