SQL: Select Duplicate Values using Distinct, Not Exists, CTE, Union

Question

I need to select duplicate values highlighted in green from the Object table show below:

I've tried different combination of the code below. But cannot return the two duplicate rows.

;with CTE as
    (Select distinct ID, count([Object ID]) as [Object ID] 
from #Object 
     group by ID having count([Object ID]) > 1)

select * from CTE where 
    NOT EXISTS (Select distinct ID , count(distinct [Object ID]) as [Object ID] 
from #Object group by ID having count(distinct [Object ID]) > 1);

Answer 1

You can use the window function ROW_NUMBER() to identify duplicate rows.

Declare @YourTable table (ID int,ObjectID int,ObjectName varchar(50))
Insert into @YourTable values
(250708,321,'hotel'),
(250708,343,'mercantile'),
(250708,370,'parking'),
(250708,370,'residential condominium'),
(250708,370,'residential condominium'),
(250708,401,'residential condominium'),
(250708,401,'residential condominium')



;with cte as (
    Select *
          ,RN = Row_Number() over ( Partition By ID,ObjectID,ObjectName Order by (Select NULL))
     From  @YourTable
)
Select * 
 From cte 
 Where RN>1

Returns

On a side-note, you can delete these records by replacing the final Select * with DELETE

Answer 2

Below is the correct edition of the code in the first answer

;With cte as (
        Select *
              ,RN = Row_Number() over (Partition By ID, [Object ID] Order by (Select NULL))
       From tblObject 
     )

    Select *
     From cte 
     Where RN>1;