I need to identify duplicate records in a table. TableA [ id, firstname, surname] I’d 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
I’m not sure how to write this query, can someone help? Thanks in advance!
Use a subquery with the HAVING clause to isolate duplicated firstname/lastname records, then link to the table to get id values:
inner join --YourSubquery
count(*) as Occurances
group by firstname,
having count(*) > 1) YourSubquery
on YourTable.firstname = YourSubquery.firstname
and YourTable.surname = YourSubquery.surname
If it's not practically useful, then it's practically useless.