NULLs IN ROW_NUMBER ( ) OVER OVER' Partition By list

  • 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.”

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

  • 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.”

  • 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.”

  • 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

  • 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