SQL Query for testing duplicate values in an attribute field

Question

I am attempting to create an SQL query that will return values that are duplicate/identical in an attribute field...Anyone have any input or wanna take a stab at this..please feel free

Thanks!!

ThomasColson · Answer

Here's one I use to find duplicate coordinates: WITH
EventsCTE(loc_name,x_coord, y_coord, Ranking)
AS(SELECT
loc_name,x_coord, y_coord, Ranking =DENSE_RANK()
OVER(PARTITIONBY x_coord, y_coord ORDERBYNEWID()ASC)
FROM
dbo.tbl_locations )Select*FROM EventsCTE WHERE Ranking > 1 and x_coord isnotnull

Or
SELECT
 a.*

FROM
  dbo.TBL_LOCATIONS  As

INNER
  
JOIN
  
(
  
SELECT X_Coord
      
, Y_Coord
  
FROM   dbo.TBL_LOCATIONS 
  
GROUP
BY X_Coord
       
, Y_Coord

HAVINGCount(*)>1
 
)As 
   
ON a.X_Coord = b.X_Coord
   
AND a. Y_Coord= b.Y_Coord

Or:

WITH
 t(OBJECTID, Ranking)

AS

(

SELECT
     OBJECTID, Ranking =DENSE_RANK()

OVER
(PARTITIONBY OBJECTID ORDERBYNEWID()ASC)

FROM
 dbo.TWIN_CREEKS_UNDERGROUND_UTILITIES)deleteFROM t WHERE Ranking > 1

JoeBorgione · Answer

If its just a one time deal and you just want to see if you have multiple instances of the same value, you can simply create a summary table on the attribute.  It'll give you a count value.

VinceAngelo · Answer

You mean, something like:
SELECT col1,count(col1) cnt FROM tab1 GROUP BY col1 HAVING count(col1) > 1

A google on "SQL duplicate values" will provide lots of other ways.

- V