topic Re: SQL Query for testing duplicate values in an attribute field in Data Management Questions

SQL Query for testing duplicate values in an attribute field

benberman — Fri, 16 Aug 2013 16:03:21 GMT

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!!

Re: SQL Query for testing duplicate values in an attribute field

VinceAngelo — Sun, 12 Dec 2021 08:09:23 GMT

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

Re: SQL Query for testing duplicate values in an attribute field

JoeBorgione — Sun, 18 Aug 2013 11:31:46 GMT

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.

Re: SQL Query for testing duplicate values in an attribute field

ThomasColson — Sun, 12 Dec 2021 08:09:25 GMT

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

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