find duplicates that is not exactly duplicates

Question

find duplicates that is not exactly duplicates

mathewspsimon

SSCommitted

Points: 1721
More actions
May 17, 2019 at 8:06 pm

#3644801

Hi All,
Need a query to find the duplicates which is not exactly duplicate- Have a person table which got some duplicate names even though the Primary is distinct. The rows are as flows-
PersonID    FName Lname
1    Tom Little
2    Tom Little
3    Tim Fisher
4    Matt George
--- So I need to find the duplicate Fname and Lname clubbed together.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

rVadim Hall of Fame Points: 3969 More actions · Answer 1

If I understood you correctly:

SELECT FName, Lname, COUNT(*)
FROM YourTable
GROUP BY FName, Lname
HAVING COUNT(*)  > 1

--Vadim R.

mathewspsimon SSCommitted Points: 1721 More actions · Answer 2

Hi rVadim, I also need the PersonIDs too so that I can delete these duplicates in the end without affecting others.

Sue_H SSC Guru Points: 90860 More actions · Answer 3

If the duplicates are just based on Fname, Lname and which of the duplicates you delete doesn't matter, you can use a CTE along the lines of:

WITH cteOfYourTable
(PersonID, FName, Lname, RowNum)
AS
(SELECT 
PersonID, 
Fname, 
Lname, 
ROW_NUMBER() OVER (PARTITION BY Fname, Lname ORDER BY PersonID) as RowNum
FROM YourTable)

DELETE 
FROM cteOfYourTable
WHERE RowNum > 1

You would of course want to select first to see if it's going to delete what you want so remove the delete and just change that to a select first to verify.

WITH cteOfYourTable
(PersonID, FName, Lname, RowNum)
AS
(SELECT 
PersonID, 
Fname, 
Lname, 
ROW_NUMBER() OVER (PARTITION BY Fname, Lname ORDER BY PersonID) as RowNum
FROM YourTable)

SELECT 
PersonID, 
Fname, 
Lname,
RowNum
FROM cteOfYourTable
WHERE RowNum > 1

Sue

mathewspsimon SSCommitted Points: 1721 More actions · Answer 4

yes, thank you Sue. That is what I was looking for.