declare @ClientReminders table ( [clr_pk] [uniqueidentifier] NOT NULL, [clr_clifk] [uniqueidentifier] NOT NULL, [clr_type] [smallint] NOT NULL, [clr_date] [date] NOT NULL, [clr_actioned] [date] NULL, [clr_empfk] [uniqueidentifier] NULL, [clr_covfk] [uniqueidentifier] NULL, [clr_pedfk] [uniqueidentifier] NULL, [clr_pplfk] [uniqueidentifier] NULL, [clr_eidfk] [uniqueidentifier] NULL ) insert into @ClientReminders values ('7E8746DF-6B9C-F84C-324A-6862DC00B62A' ,'7A2D25C8-3C95-884A-0184-B80AED1F995D' ,3 ,'2017-08-29' ,NULL ,'7FE26FBE-A4D1-6A4E-2C48-674E686BDD42' ,NULL ,NULL ,NULL ,'70AE0219-2A17-244D-3949-F93B3BC92954') insert into @ClientReminders values ('63B2B21F-8B9B-6D40-18C4-8AC6EDD28DA5' ,'7A2D25C8-3C95-884A-0184-B80AED1F995D' ,3 ,'2017-08-29' ,NULL ,'7FE26FBE-A4D1-6A4E-2C48-674E686BDD42' ,NULL ,NULL ,NULL ,'70AE0219-2A17-244D-3949-F93B3BC92954') insert into @ClientReminders values ('7A6B3989-C44C-5448-2BD4-BB97E02E9B5E' ,'7A2D25C8-3C95-884A-0184-B80AED1F995D' ,3 ,'2017-10-02' ,NULL ,'72F7E803-3921-8D47-37F3-E3EEC599784D' ,NULL ,NULL ,NULL ,'B83600FD-3E43-C34C-0F0B-7FD3E3F7D8FE') insert into @ClientReminders values ('3B374CA2-F806-8141-26DF-F9BA7AD404B5' ,'7A2D25C8-3C95-884A-0184-B80AED1F995D' ,3 ,'2017-10-02' ,NULL ,'72F7E803-3921-8D47-37F3-E3EEC599784D' ,NULL ,NULL ,NULL ,'B83600FD-3E43-C34C-0F0B-7FD3E3F7D8FE') ;with cte as (select *, row_number() over (partition by [clr_clifk] ,[clr_type] ,[clr_date] ,[clr_actioned] ,[clr_empfk] ,[clr_covfk] ,[clr_pedfk] ,[clr_pplfk] ,[clr_eidfk] order by [clr_clifk] ,[clr_type] ,[clr_date] ,[clr_actioned] ,[clr_empfk] ,[clr_covfk] ,[clr_pedfk] ,[clr_pplfk] ,[clr_eidfk] ) as Rn from @ClientReminders) /* this gives me 2 rows select cte.* from cte where rn > 1 */ -- this gives me no rows and I can't see why select keep.clr_pk as KeepId, dup.clr_pk as DupId ,dup.[clr_clifk] ,dup.[clr_type] ,dup.[clr_date] ,dup.[clr_actioned] ,dup.[clr_empfk] ,dup.[clr_covfk] ,dup.[clr_pedfk] ,dup.[clr_pplfk] ,dup.[clr_eidfk] from cte dup inner join (SELECT * from cte WHERE Rn = 1) keep on dup.[clr_clifk] = keep.clr_clifk and dup.[clr_type] = keep.[clr_type] and dup.[clr_date] = keep.[clr_date] and dup.[clr_actioned] = keep.[clr_actioned] and dup.[clr_empfk] = keep.[clr_empfk] and dup.[clr_covfk] = keep.[clr_covfk] and dup.[clr_pedfk] = keep.[clr_pedfk] and dup.[clr_pplfk] = keep.[clr_pplfk] and dup.[clr_eidfk] = keep.[clr_eidfk] delete @clientremindersAnybody see what I a cannot see after staring at this too long?