Home Forums SQL Server 2017 SQL Server 2017 - Development Compare two rows in the same table for equality in SQL RE: Compare two rows in the same table for equality in SQL

  • January 22, 2018 at 7:17 am

    #1976719

    Paulo de Jesus - Sunday, January 21, 2018 7:38 AM

    I'm guessing this is what you are looking for:


    WITH
    Duplicates
    AS
    (
        SELECT    [Policy]
        FROM    Insurance
        GROUP BY [Policy]
        HAVING COUNT(ID) > 1
    )

    SELECT    I.ID, I.[Policy]
    FROM    Insurance I
    INNER JOIN
            Duplicates D
        ON    D.[Policy] = I.[Policy]

    There's a more efficient way to handle this particular query.  The original requires two scans of the table, once for the CTE and then once for the main query.  The rewrite only requires one scan of the table.

    WITH Duplicates AS
    (
      SELECT  ID, [Policy], COUNT(*) OVER(PARTITION BY [Policy]) AS cnt
      FROM  Insurance
    )

    SELECT ID, [Policy]
    FROM Duplicates
    WHERE cnt > 1
    ;

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA