25 May 2010

Finding double entries like RecIds via SQL

If you need to find double entries in an SQL DB u can use the following statement:
SELECT COUNT(RECID), RECID
  FROM [ADDRESSSTATE]
GROUP BY RECID HAVING count(RECID) > 1
Another example finding adresses in different countries:
SELECT count([COUNTRYREGIONID]), COUNTRYREGIONID      
  FROM [dbo].[ADDRESS]
  GROUP By COUNTRYREGIONID having COUNT([COUNTRYREGIONID]) > 1
GO
Or to find dublicates for a multi column key
SELECT [DIMENSIONATTRIBUTE]
      ,[ENTITYINSTANCE]
      ,[ISDELETED]
      ,count(DIMENSIONATTRIBUTE)
FROM [dbo].[DIMENSIONATTRIBUTEVALUE]
GROUP BY DIMENSIONATTRIBUTE, ENTITYINSTANCE, ISDELETED HAVING count(DIMENSIONATTRIBUTE) > 1  
Post a Comment