Delete duplicates within a group of a group

Question

I have a table t_objectproperties in a MSSQL database like follows

PropertyID	ObjectID	Property
ID1	Object1	Property1
ID2	Object1	Property1
ID3	Object1	Property1
ID4	Object2	Property1

I would like to delete all duplicates of a property within the same object. So the rows (ID2, Object1, Property1 AND ID3, Object1, Property1) should be deleted while the rows (ID1, Object1, Property1 AND ID4, Object2, Property1) must not be deleted. I always want to keep the lowest PropertyID of the multiple Properties within the same object.

Until now I tried the following code but didn't get a satisfying result. (I tried with SELECT instead of DELETE, in order to first confirm that my statement is correct)

WITH CTE AS(
   SELECT t_objectproperties.Property, t_objectproperties.ObjectID, t_objectproperties.PropertyID,
       RN = ROW_NUMBER()OVER(PARTITION BY t_objectproperties.Property ORDER BY t_objectproperties.PropertyID ASC)
   FROM t_objectproperties
)
SELECT * FROM CTE WHERE RN > 1

If I partition by ObjectID the result is different but still not what I'm looking for.

Further I tried extracting the correct format of my data with the following statement.

SELECT t_objectproperties.ObjectID, t_objectproperties.Property, count(*)
From t_objectproperties
Group by t_objectproperties.ObjectID, t_objectproperties.Property

While the groupy by extracts the data correctly, I can't use that statement to delete my rows. Somehow I need to combine the two statements.

I've found a lot of answers here and on google, but they never wanted to group/partition by 3 columns and then delete it.

How can I extract/delete the respective data?

Don't you need to `PARTITION BY` both `ObjectID` *and* `Property`? — Thom A, Aug 10 '23 at 09:16
Does this answer your question? [How to delete duplicate rows in SQL Server?](https://stackoverflow.com/questions/18390574/how-to-delete-duplicate-rows-in-sql-server) — Thom A, Aug 10 '23 at 09:17

score 2 · Answer 1 · answered Aug 10 '23 at 09:26

Well that was way easier than expected. Thanks a lot Thom for your comment. I had to partition by ObjectID and Property. The following code worked as a charm.

WITH CTE AS(
   SELECT t_objectproperties.Property, t_objectproperties.ObjectID, t_objectproperties.PropertyID,
       RN = ROW_NUMBER()OVER(PARTITION BY t_objectproperties.ObjectID, t_objectproperties.Property ORDER BY t_objectproperties.PropertyID ASC)
   FROM t_objectproperties
)
Delete FROM CTE WHERE RN > 1

Thanks.

Delete duplicates within a group of a group

1 Answers1