Paulo de Jesus - Sunday, January 21, 2018 7:38 AM
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