SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to remove duplicates using CTE in Table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mohan123
Constraint Violating Yak Guru

India
252 Posts

Posted - 11/16/2012 :  07:35:25  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/16/2012 :  08:03:39  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000