Level Extreme platform
Subscription
Corporate profile
Products & Services
Support
Legal
Français
Duplicates or Not?
Message
From
10/10/2017 09:45:10
 
 
To
All
General information
Forum:
Microsoft SQL Server
Category:
SQL syntax
Title:
Duplicates or Not?
Environment versions
SQL Server:
SQL Server 2012
Miscellaneous
Thread ID:
01654877
Message ID:
01654877
Views:
68
I am trying to check for duplicates and am not understanding why when I run the first commented out query below I get 2 rows back, yet when I try to join those rows back to their duplicate row I get no rows.

Here's my script:
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 @clientreminders
Anybody see what I a cannot see after staring at this too long?
Frank.

Frank Cazabon
Samaan Systems Ltd.
www.samaansystems.com
Next
Reply
Map
View

Click here to load this message in the networking platform