Stack Overflow Asked by Jason Livengood on December 16, 2021
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
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.
Answered by Gordon Linoff on December 16, 2021
See Preben's answer for how to find dups.
To avoid dups altogether consider creating an unique index.
Answered by Z.D. on December 16, 2021
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
Answered by Preben Huybrechts on December 16, 2021
Get help from others!
Recent Answers
Recent Questions
© 2024 TransWikia.com. All rights reserved. Sites we Love: PCI Database, UKBizDB, Menu Kuliner, Sharing RPP