How do I find a duplicate in SQL

Question

I have a query that selects 3 columns. Each row row should be a unique combination of county, city,and zip. However, I have reason to believe I'm getting a duplicate somewhere. How do I find the duplicate ? COUNT() ?? This in MS SQL Server . Any help would be most appreciated. --Jason

SELECT  COUNTY, CITY, ZIP 
FROM MoratoriumLocations
WHERE MoratoriumID=20
ORDER BY County

Group by County, city, zip should give you a unique combination. — Namandeep_Kaur, Jul 23 '20 at 18:11
check this answer might help you: https://stackoverflow.com/questions/2594829/finding-duplicate-values-in-a-sql-table — Murilo Góes de Almeida, Jul 23 '20 at 18:12

Preben Huybrechts · Accepted Answer · 2020-07-23T18:59:46.067

You coul use group by and having

SELECT  COUNTY, CITY, ZIP 
FROM MoratoriumLocations
WHERE MoratoriumID=20
GROUP BY COUNTY, CITY, ZIP
HAVING COUNT(1) >1
ORDER BY County

If you want to get the full row details you can use a sub query in combination with the group by and having statements

SELECT x.*
FROM MoratoriumLocations x
INNER JOIN( 
  SELECT  COUNTY, CITY, ZIP 
  FROM MoratoriumLocations
  WHERE MoratoriumID=20
  GROUP BY COUNTY, CITY, ZIP
  HAVING COUNT(1) >1
) dups ON dups.County = x.County
  AND dups.City = x.City
  AND dups.Zip = x.Zip

score 0 · Answer 2 · answered Jul 23 '20 at 19:23

0

See Preben's answer for how to find dups.

To avoid dups altogether consider creating an unique index.

answered Jul 23 '20 at 19:23

Z .

12,657
1
31
56

score 0 · Answer 3 · answered Jul 24 '20 at 00:20

I would suggest window functions:

SELECT ml.*
FROM (SELECT ml.*, COUNT(*) OVER (PARTITION BY County, City, Zip) as cnt
      FROM MoratoriumLocations ml
      WHERE MoratoriumID = 20
     ) ml
ORDER BY cnt DESC, County, City, Zip;

This will show the complete rows with duplicates, which can help you understand them better.

How do I find a duplicate in SQL

3 Answers3