duplicate record

Dear All,

I need to identify duplicate records in a table. TableA [ id, firstname, surname] Id like to see records that may be duplicates, meaning both firstname and surname are the same and would like to know how many times they appear in the table

Im not sure how to write this query, can someone help? Thanks in advance!try something like this:

select id, fname, lastname, count(*)
from tablename
having count(*) > 1|||Use a subquery with the HAVING clause to isolate duplicated firstname/lastname records, then link to the table to get id values:

select YourTable.id,
YourTable.firstname,
YourTable.surname,
YourSubquery.occurances
from YourTable
inner join --YourSubquery
(select firstname,
surname,
count(*) as Occurances
from YourTable
group by firstname,
surname
having count(*) > 1) YourSubquery
on YourTable.firstname = YourSubquery.firstname
and YourTable.surname = YourSubquery.surname|||try something like this:

select id, fname, lastname, count(*)
from tablename
having count(*) > 1

Hmm ok, doesn't look complex at all, thanks!

It gave me error messages about not having a grouped by statement in there, so I added it. It works fine, thanks a lot!sql