Post reply

Removing Duplicates

  • November 25, 2009 at 10:56 am

    #228173

    I'm trying to remove duplicates from a query result. Here is an example and then I'll explain:

    Column_A Column_B Column_C

    ---------- ---------- ---------

    1000004658 E02 False

    1000004658 999 False

    If I use the distinct clause this will not weed out the duplicates because row 1 and 2 are not the same. I want to remove both rows if only column A is the same. Using the group by clause does not work because I want to select all 3 columns and only group by columnA having a count > 1.

    Any help would be appreciated?

  • November 25, 2009 at 11:15 am

    #1084230

    It's not totally clear what you want, but try this

    WITH CTE AS(

    SELECT Column_A,Column_B,Column_C,

    COUNT(*) OVER(PARTITION BY Column_A) AS cn

    FROM mytable)

    SELECT Column_A,Column_B,Column_C

    FROM CTE

    WHERE cn=1

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

  • November 25, 2009 at 11:58 am

    #1084257

    Thanks you ever so much. This solved my problem.

  • November 25, 2009 at 12:52 pm

    #1084291

    Now the user would like me to get rid of both records in question since we have identified this as being a duplicate record. How would I do that?

    Thanks

  • November 25, 2009 at 2:21 pm

    #1084356

    dale_keller (11/25/2009)

    Now the user would like me to get rid of both records in question since we have identified this as being a duplicate record. How would I do that?

    Thanks

    Try this

    WITH CTE AS(

    SELECT Column_A,Column_B,Column_C,

    COUNT(*) OVER(PARTITION BY Column_A) AS cn

    FROM mytable)

    DELETE

    FROM CTE

    WHERE cn>1

    ____________________________________________________

    Deja View - The strange feeling that somewhere, sometime you've optimised this query before

    How to get the best help on a forum

    http://www.sqlservercentral.com/articles/Best+Practices/61537

Viewing 5 posts - 1 through 4 (of 4 total)

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