Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 339
  • Last Modified:

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
0
zolf
Asked:
zolf
  • 4
  • 2
2 Solutions
 
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 CroweCommented:
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
 
zolfAuthor Commented:
thanks a lot. it worked as expected. now if I want to delete the duplicate records how can I do it
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
Brian CroweCommented:
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
 
Ephraim WangoyaCommented:
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:
cheers
0

Featured Post

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now