need query help - finding duplicate records

[jacksonpt]

I've got a database that collects user registrations for various events. From time to time, I need to go through the list of registrations and look for duplicate entries.

Currently I do

SELECT RegID, FirstName, LastName, Email, from db order by LastName, FirstName, Email, RegID;

I go though each record last name by last name looking for duplicates. It works, but it's slow and tedious - I'd like to find a better way.

I thought about trying to find only the duplicated last names... that way I wouldn't have to read through the records with unique last names. I tried

Select RegID, FirstName, LastName, Email from db where count(distinct LastName) > 1;

but I got an error, Illegal use of the group by function.

Can someone help with this query? Or is there a better way to look for duplicates? Thanks.

[wreed]

This article should help http://www.databasejournal.com/features/mysql/article.php/10897_2201621_2

[qolbu]

SELECT LastName FROM db WHERE EXISTS (SELECT NULL FROM db b WHERE b.lastname = db.LastName GROUP BY b.LastName HAVING db.RegID < MAX(b.RegID))

hope that help.