Find duplicate fields in table

Hello there,

I am trying to find duplicate fields in my table. I am using mssql 2008
I tried something like this but it does not return any records.

SELECT province,county,districtNcity,citycode,count(*) 
FROM fdocode
group by province,county,districtNcity,citycode
having count(*) > 1

Open in new window


BUT when I try this it returns many records

SELECT districtNcity,count(districtNcity) 
FROM fdocode
group by districtNcity
having count(districtNcity) > 1

Open in new window


I want the query to return all the cols and find duplicates in all the cols.

cheers
Zolf
zolfAsked:
Who is Participating?
 
Ephraim WangoyaConnect With a Mentor Commented:
Use this

with cte as
(
 select *, row_number() over (partition by districtNcity, county order by ID) [RN]
 from fdocode
)

delete from cte
where RN > 1

Open in new window

0
 
zolfAuthor Commented:
I tried something like this and it worked to some extent. But I need to compare 2 cols for duplicate now it is only comparing one col i.e. the districtNcity col. I need to compare the districtNcity and county col together.if they are same then return all the col record

SELECT DISTINCT A.ID,province,county, A.districtNcity, A.citycode, B.Count
FROM fdocode A
JOIN (
  SELECT COUNT(*) as Count, B.districtNcity
  FROM fdocode B
  GROUP BY B.districtNcity
) AS B ON A.districtNcity = B.districtNcity
WHERE B.Count > 1
ORDER by A.districtNcity;

Open in new window

0
 
Brian CroweConnect With a Mentor Database AdministratorCommented:
If i understand you correctly...

SELECT A.*
FROM fdocode AS A
INNER JOIN (
   SELECT districtNcity, county
   FROM fdocode
   GROUP BY districtNcity, county
   HAVING COUNT(*) > 1 ) AS B
   ON A.districtNcity = B.districtNcity
      AND A.county = B.county
0
Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
zolfAuthor Commented:
thanks a lot. it worked as expected. now if I want to delete the duplicate records how can I do it
0
 
Brian CroweDatabase AdministratorCommented:
Since you don't appear to have a unique value in your record (IDENTITY, GUID) you will have to define which record you want to keep.
0
 
zolfAuthor Commented:
I do have PK. my table structure is as follows

CREATE TABLE
    fdocode
    (
        id INT NOT NULL IDENTITY,
        province NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        county NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        districtNcity NVARCHAR(255) COLLATE Latin1_General_CI_AS,
        citycode FLOAT(53)
    );

Open in new window

0
 
zolfAuthor Commented:
cheers
0
All Courses

From novice to tech pro — start learning today.