find duplicates that is not exactly duplicates

  • mathewspsimon

    SSCommitted

    Points: 1706

    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.

  • rVadim

    Hall of Fame

    Points: 3938

    If I understood you correctly:

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

     

    --Vadim R.

  • mathewspsimon

    SSCommitted

    Points: 1706

    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: 90287

    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: 1706

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

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

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