Post reply

Another Removing Duplicates Problem

  • June 30, 2005 at 3:03 am

    #89928

    Hi (SQL Server 2000)

    Another problem removing duplicates that I can't figure out how to do !

    Table tblPhoneFaxEmail

     

    structure

    PhoneFaxEmailID (int)

    PhoneFaxEmailTypeID (int)

    PeopleID (int)

    PhoneFaxEmail (varchar)

     

    Data

    1   4   65   0123456

    2   4   65   019282726

    3   5   65   someone@x.com

    4   4   36   0123456

    5   5   36    someone@x.com

    6   5   101  some2@x.com

     

    I want to return 1 of every different PhoneFaxEmail column together with its type and ID, if there is more than one, it does not matter which ID I get so long as I only get 1  eg

    1   4   65   0123456

    2   4   65   019282726

    3   5   65   someone@x.com

    6   5   101  some2@x.com

    The reason I am doing this is I am extracting from a legacy system into a new system where I will set up proper constraints.

    Thanks

    CCB

  • June 30, 2005 at 6:08 am

    #569726

    Something similar was discussed just yesterday so this's still fresh in my mind:

    Select A.* from tblPhoneFaxEmail A

    inner join

    (select PhoneFaxEmail, min(PhoneFaxEmailID) PhoneFaxEmailID from tblPhoneFaxEmail

    Group By PhoneFaxEmail) B

    on

    A.PhoneFaxEmailID = B.PhoneFaxEmailID

    Order By A.PhoneFaxEmailID

    .....Thanks Noel!







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 30, 2005 at 6:50 am

    #569746

    Thanks Noel.

    CCB

  • June 30, 2005 at 7:26 am

    #569774

    You should have thanked sushila 

    She just remembered some post we had fun with but She was the one who replied to you, not me

    Cheers,

     

     


    * Noel

  • June 30, 2005 at 7:38 am

    #569789

    Noel - Reestablishing "ownership chains" of thank yous ?!?! <;-)







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 30, 2005 at 7:49 am

    #569801

    Woops, sorry about that.

    Thank you sushila.

    CCB

  • June 30, 2005 at 7:49 am

    #569800

    Sure! Are we dbas or what ?  


    * Noel

  • June 30, 2005 at 8:17 am

    #569825

    Don't worry about it Charlotte - it happens all the time!

    Last week I responded to a post and the poster thanked Remi - <:-(







    **ASCII stupid question, get a stupid ANSI !!!**

  • June 30, 2005 at 10:17 am

    #569948

    Cheers Frank

    Noel: Or What........

     

     

     

    Steve

    We need men who can dream of things that never were.

  • June 30, 2005 at 10:37 am

    #569978

    exactly right David!







    **ASCII stupid question, get a stupid ANSI !!!**

  • July 1, 2005 at 6:19 am

    #570283

    My favorite technique for removing duplicates, at least for larger data sets, is to use CREATE UNIQUE INDEX ... WITH IGNORE_DUP_KEY.

    You can use ORDER BY when filling the table to control which records are discarded.

Viewing 11 posts - 1 through 10 (of 10 total)

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