2

I have been trying to find duplicate county names in a state according to StateId. I have tried to write a SQL query that helps to find duplicate data, but I could not.

SELECT LOWER(Name) COLLATE SQL_Latin1_General_CP1_CI_AI, StateId FROM County (nolock) GROUP BY Name, StateId HAVING (COUNT(*) > 1)

I would be very happy if someone could help.

enter image description here

Improve this question
7
  • 3
    GROUP BY LOWER(Name) collate SQL_Latin1_General_CP1_CI_AI, StateId. Commented Feb 3, 2021 at 10:31
  • Are names maybe different in Turkish colaltion? If so, group by the same field as you have in the select section. Commented Feb 3, 2021 at 10:32
  • 3
    Side note: Do you really need NOLOCK? YOu *do understand what it does, right? For what you're doing, I'd suggest that it is actually likely to give you incorrect results if work on the data in the table is being done. Commented Feb 3, 2021 at 10:33
  • Please be aware of different names you have in the example. For example I see words "BAGCILAR" and "ATASEHIR" written in at at least 2 different ways Commented Feb 3, 2021 at 10:36
  • 1
    Sounds like you also might want to add a constraint on your table to stop duplicate values. Commented Feb 3, 2021 at 10:39

1 Answer 1

Reset to default
2

If you looking for duplicate counties and duplicate counties per stateid, you need to remove the stateid from the group by query. If you're looking for case-insensitive duplicates, you need to group by the lower(name), like you're querying:

SELECT LOWER(Name) COLLATE SQL_Latin1_General_CP1_CI_AI FROM county(nolock) GROUP BY LOWER(Name) COLLATE SQL_Latin1_General_CP1_CI_AI HAVING COUNT(*) > 1
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.