How to remove duplicates using CTE in Table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

How to remove duplicates using CTE in Table

Author

Topic

mohan123
Constraint Violating Yak Guru

252 Posts

Posted - 2012-11-16 : 07:35:25

i have a table with huge data i need to remove duplicates from that table

when i am using this query i am getting different set
SELECT Gender,GivenName,Surname,
COUNT(*) AS NO_OF_DUPLICATE_RECORDS
FROM HugeNames
where NameID is not null
GROUP BY Gender,GivenName,Surname
HAVING COUNT(*) > 1

after writing CTE :

;WITH cte
as
(
SELECT GivenName, Surname,MiddleInitial
FROM HugeNames
WHERE rtrim(ltrim(GivenName)) like '%%'
AND NameID is not null
AND ltrim(rtrim(Surname)) like '%%'
and GivenName is not null
and Surname is not null
GROUP BY GivenName, Surname,MiddleInitial
HAVING COUNT(*) > 1
)
SELECT u.GivenName,u.Surname,NameID,u.MiddleInitial
from HugeNames u

INNER JOIN cte
ON u.GivenName=cte.GivenName
AND u.Surname=cte.Surname
AND u.MiddleInitial=cte.MiddleInitial
where u.GivenName is not null
AND u.Surname is not null
AND u.MiddleInitial is not null
ORDER BY GivenName,Surname,MiddleInitial ASC

in this how do i remove the Duplicates suggest me ???

P.V.P.MOhan

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-11-16 : 08:03:39

One of the two below:

/************************************************************
 * Code formatted by SoftTree SQL Assistant © v6.2.112
 * Time: 11/16/2012 8:00:29 AM
 ************************************************************/
-- 1
SELECT Gender,
       GivenName,
       Surname
FROM   HugeNames
WHERE  NameID IS NOT     NULL
GROUP BY
       Gender,
       GivenName,
       Surname

-- 2
;WITH cte
AS
(
    SELECT GivenName,
           Surname,
           MiddleInitial,
          NameID,
           ROW_NUMBER() OVER 
			( PARTITION BY GivenName,Surname,MiddleInitial ORDER BY (SELECT NULL) ) AS RN
    FROM   HugeNames
    WHERE  RTRIM(LTRIM(GivenName)) LIKE '%%'
           AND NameID IS NOT NULL
           AND LTRIM(RTRIM(Surname)) LIKE '%%'
           AND GivenName IS NOT NULL
           AND Surname IS NOT NULL
) 
SELECT u.GivenName,
       u.Surname,
       NameID,
       u.MiddleInitial
FROM   cte
WHERE  RN = 1
ORDER BY
       GivenName,
       Surname,
       MiddleInitial ASC

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources