CODEWITHSUNDEEP
sqlsql-serverselectduplicates
Ismail Dogan
Ismail Dogan

Reputation: 325

Find duplicate county name in SQL Server

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

Upvotes: 2

Views: 277

Answers (1)

Mureinik
Mureinik

Reputation: 311528

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

Upvotes: 2

Related Questions