October 11, 2011 at 1:48 pm
What I understand is you want to make use of other columns irrespective of null in participating column. Please let me know if my understanding is correct.
Either use
to make it a unique key
OR
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 11, 2011 at 2:17 pm
You are right. Null does not affect the result, so isnull is not necessary (especially - I do not want to change the data, I just want to remove duplicates, e.g. delete where rNum>1 )
October 11, 2011 at 10:21 pm
WITH CTE
AS
(SELECT [SITE]
,[Hospital]
,[LOCATION]
,[Real_Date_Time]
,[NURSE_ID]
,[PRIMARY_NAME]
,[ERROR_CODE]
,[PMP]
,[PAT_NUM]
,[UniqueID]
,[PTNAME]
,[SCANNED_DATA]
,[DRUG_DESC_SCAN_ERR]
,[Sig]
,[Sig_Desc]
,[Frequency]
,[SCH_PRN]
,ROW_NUMBER() over (partition by
datepart(hour,real_date_time)
,nurse_ID
,pat_num
,pmp <== may be null
,ERROR_CODE
order by
datepart(hour,real_date_time)
,nurse_ID
,Pat_num
,pmp
,ERROR_CODE) rNum
FROM LoadTblAllDatanoDuplicates, etc
)
DELETE FROM CTE WHERE ROWNUM > 1
AND PMP IS NOT NULL
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 13, 2011 at 3:16 am
Velkry Was it helpful ?
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
October 13, 2011 at 8:53 am
Thank you very much. I've solved the problem even before I posted my question. The scenario was
1) cut duplicatable records from the base table,
2) place them into a temporary table and rank them using the code a fragment of which i posted,
3) insert into base table all the records with rank (rNum)=1
My only concern was if one of the fields have a null value - how query engine is going to handle it. In other words, I was not sure that a record would be included in the result set if one (or more, but in my case only one) has a null value.
And your line of logics is absolutely correct (if not to mention that i do NOT want null values containing records to be excluded from the result set).
Cheers
Val
October 13, 2011 at 10:50 am
2) place them into a temporary table and rank them using the code a fragment of which i posted,
3) insert into base table all the records with rank (rNum)=1
My only concern was if one of the fields have a null value - how query engine is going to handle it. In other words,
Thanks vel.
______________________________________________________________________
Ankit
MCITP Database developer SQL 2008,MCTS Database Administration SQL 2008
"Remembering that you are going to die is the best way I know to avoid the trap of thinking you have something to lose.
You are already naked. There is no reason not to follow your heart.”
Viewing 6 posts - 16 through 20 (of 20 total)
You must be logged in to reply to this topic. Login to reply