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