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
Go to Top of Page
   

- Advertisement -