3,725 13

SQL Duplicates

Copy Embed Code
<iframe id="embedFrame" style="width:600px; height:300px;"
Click on the embed code to copy it into your clipboard Width Height
Leave empty to retrieve all the content Start End
WITH cte AS (SELECT *, ROW_NUMBER() OVER (PARTITION BY col1, col2, col3, col4 ORDER BY (SELECT 0)) AS DuplicateRowNumber FROM table ) SELECT FROM cte WHERE DuplicateRowNumber > 1 or---- WITH cte as (SELECT PEOPLE.*, Loan.LoanID, ROW_NUMBER() OVER (PARTITION BY SocialSecurityNo ORDER BY SocialSecurityNo) AS DuplicateRowNumber FROM people INNER JOIN LOAN ON FK_PeopleID=PeopleID WHERE ISNULL(SocialSecurityNo,'') <> '' AND SocialSecurityNo <> '--' AND SocialSecurityNo <> '000-00-0000' AND loan.Status='active' ) SELECT * INTO #TEMP99 FROM CTE WHERE cte.DuplicateRowNumber > 1 SELECT *,b.SocialSecurityNo FROM dbo.Loan INNER JOIN (SELECT PeopleID,SocialSecurityNo FROM PEOPLE WHERE SocialSecurityNo IN (SELECT SocialSecurityNo FROM #temp99)) b ON B.PeopleID=LOAN.FK_PeopleID WHERE LOAN.Status='ACTIVE' DROP TABLE #TEMP99 -- Another way --GET RID OF #NewAddressCandidates ADDESSES ALEADY IN THE ADDRESS TABLE DELETE FROM #NewAddressCandidates WHERE EXISTS ( SELECT t.[Address1] ,t.Address2 ,t.City ,t.[State] ,SUBSTRING(t.Zip,1,5) AS ZIP, SUBSTRING(t.Zip,7,4) AS ZIP4 FROM #NewAddressCandidates T INTERSECT SELECT [Address1] ,[Address2],[City],[State],[Zip],ZIP4 FROM dbo.[Address]) -- Another way WITH NODUP AS (SELECT A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4 , row_number() OVER ( PARTITION BY A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4 ORDER BY A.ADDRESS1,A.ADDRESS2,A.CITY,A.STATE,A.ZIP,A.ZIP4 ) AS nr FROM #NewAddressCandidates A) DELETE FROM NODUP WHERE NR > 1; SELECT * INTO #UniqueNewAddresses FROM #NewAddressCandidates WHERE EXISTS ( SELECT t.[Address1] ,t.Address2 ,t.City ,t.[State] ,SUBSTRING(t.Zip,1,5) AS ZIP, SUBSTRING(t.Zip,7,4) AS ZIP4 FROM #NewAddressCandidates T EXCEPT SELECT [Address1] ,[Address2],[City],[State],[Zip],ZIP4 FROM dbo.[Address]) // Now to remove the duplicate rows, use the same table and use EXCEPT operator with an // empty result set returned by the same table // USING except operator SELECT col1,col2 FROM DuplicateRcordTable EXCEPT SELECT col1,col2 FROM DuplicateRcordTable WHERE 1=0
If you want to be updated about similar snippets, Sign in and follow our Channels

blog comments powered by Disqus