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 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy