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

Mureinik
  • 297,002
  • 52
  • 306
  • 350
Ismail Dogan
  • 295
  • 2
  • 20
  • 3
    `GROUP BY LOWER(Name) collate SQL_Latin1_General_CP1_CI_AI, StateId`. – jarlh Feb 03 '21 at 10:31
  • Are names maybe different in Turkish colaltion? If so, group by the same field as you have in the select section. – Marko Juvančič Feb 03 '21 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. – Thom A Feb 03 '21 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 – ekochergin Feb 03 '21 at 10:36
  • 1
    Sounds like you also might want to add a constraint on your table to stop duplicate values. – Thom A Feb 03 '21 at 10:39
  • Do you really need `COLLATE` if you are using `LOWER()`? – Gordon Linoff Feb 03 '21 at 12:14
  • Stop splattering your code with [nolock](https://www.sentryone.com/blog/aaronbertrand/bad-habits-nolock-everywhere). But if you DO and you accept the consequences, then at least use the current syntax and not the deprecated syntax. – SMor Feb 03 '21 at 12:46

1 Answers1

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
Mureinik
  • 297,002
  • 52
  • 306
  • 350