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);

John Cappelletti · Answer 1 · 2017-01-12T23:08:33.287

8

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

edited Jan 12 '17 at 23:08

answered Jan 12 '17 at 22:13

John Cappelletti

79,615
7
44
66

This will miss the first row in any set of duplicates. – Martin Smith Jan 12 '17 at 22:14
1

@MartinSmith Only the second dupe is highlighed – John Cappelletti Jan 12 '17 at 22:15
1

@MartinSmith Encouraging to know that can happen to someone of your ranking and reputation. :) – John Cappelletti Jan 12 '17 at 22:18
John, you put have to remove parentheses after NULL and place two once aster "YourTable" . – Data Engineer Jan 12 '17 at 22:37
John, also when I parse the code it says: Command(s) completed successfully. But when I actually execute it is says: 1. Must specify table to select from. invalid columns names.... 2. Invalid column name 'ID', Invalid column name 'Object ID'... 3. Invalid column name 'Object Name', 4. No column name was specified for column 1 of 'cte' – Data Engineer Jan 12 '17 at 22:41
John, you also missed the From clause after the Order By – Data Engineer Jan 12 '17 at 22:53
@enigma6205 No I did not. The window functions are invaluable. Well worth your time to get comfortable with them. – John Cappelletti Jan 12 '17 at 22:58
John, also your Order BY does not make sense at it returns more than 1 value. Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. – Data Engineer Jan 12 '17 at 22:59

score 2 · Answer 2 · answered Jan 12 '17 at 23:18

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;

answered Jan 12 '17 at 23:18

Data Engineer

795
16
41

Well done! +1 Just a hint for the future. It is more helpful if you post sample data as text which can me copied and pasted rather than the image. This way we can test the SQL for proper field names and/or syntax, – John Cappelletti Jan 12 '17 at 23:41

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

2 Answers2