Removing Duplicates DueTo Unique Rows

  • cos_ta393 (12/2/2009)


    Good Afternoon,

    I am working with a dataset that has duplicate ID's due to unique records. The challenge is any number of column(s) can cause the uniqueness therefore there is no way to issolate a single column. The goal is to drop all dups but one and it doesnt matter which records is left.

    Below is a sample dataset.

    Thanks,

    CREATE TABLE [dbo].[DupsDueToUniqueRows](

    [ID] [int] NULL,

    [Name] [nvarchar](30) NULL,

    [Contact] [nvarchar] (30) NULL,

    [Status] [char] (1) NULL

    ) ON [PRIMARY]

    INSERT INTO [DupsDueToUniqueRows] ([ID], [Name], [Contact], [Status])

    SELECT 1, 'Jon' ,'Mobile', 'A'

    UNION ALL

    SELECT 1, 'Jon' ,'Mobile', 'B'

    UNION ALL

    SELECT 1, 'Jon' ,'Pager', 'A'

    UNION ALL

    SELECT 2, 'Paul' ,'Mobile', 'A'

    UNION ALL

    SELECT 2, 'Paul' ,'Mobile', 'B'

    UNION ALL

    SELECT 3, 'Art' ,'Mobile', 'B'

    UNION ALL

    SELECT 3, 'Art' ,'Mobile', 'A'

    UNION ALL

    SELECT 4, 'Mike' ,'Mobile', 'A'

    UNION ALL

    SELECT 4, 'Mike' ,'Pager', 'A'

    UNION ALL

    SELECT 4, 'Mike' ,'Mobile', 'B'

    UNION ALL

    SELECT 4, 'Mike' ,'Cell', 'A'

    UNION ALL

    SELECT 5, 'Art' ,'Mobile', 'A'

    GO

    Heh... I've seen everyone's solution and I have to ask... using the data from above, what would you actually like to see as a return?

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Jeff,

    What I really need is a solution that deletes the duplicate records. As I explained the dups are occurring because of uniqueness in the rows caused by any number of columns on the row. The actual table has over a hundred columns therefore I can't isolate just one. At this point the solution should just keep one of the records and delete all the others.

    Thanks

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

  • I'm just having a hard time with your description... what you really mean is that if the ID is duplicated, it's considered to be a dupe and you don't care which rows you delete for that ID so long as you just end up with one row for that ID, correct?

    Thanks for the feedback.

    --Jeff Moden


    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.


    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • Correct. Also please note that you can have more than two occurrances of the ID.

    MCTS: BI 2008, MCITP: BI 2008
    Stay Thirsty My Friends

Viewing 4 posts - 16 through 18 (of 18 total)

You must be logged in to reply to this topic. Login to reply