Post reply

Finding duplicates in a table.

  • October 11, 2006 at 9:20 pm

    #114694

    Hi all,

    Table A with following structure

    RecID, CustID, CustName, TypeID

    with the following data

    1,001,ABC,A

    2,002,XYZ,A

    3,001,ABC,B

    Now, all I need to find out if there is a record (duplicates) which match CustID or CustName for TyprID='A' with rest of the TypeID's. In other words, I have to mark duplicate customers under TypeID = 'A' with rest of the customers in the same table.

     

    Customer table is quite big, if you guys can give me codes which can run effeciently, that would be great.

     

    Thanks in advance.

  • October 11, 2006 at 9:38 pm

    #665353

    select

             col_name

             count(col_name)

      from

           customer

      group by

           col_Name

     

    Something like that will give you which ones have duplicates - depending on what columns you are tyring to work out the duplication on. If multiple columns just add more to the column list and the order by list.

    Then, once you have them you have to do something with them. Thats up to you

     

     

    Regards,

    Steve

    Life without beer is no life at all

    All beer is good, some beers are just better than others

  • October 11, 2006 at 10:00 pm

    #665359

    I am sorry. I did not made questions very clear. Let me make it bit more clear on this.

     

    I want to find duplicates from TypeID 'A' and 'B' with the rest of TypeID. Its bit more complicated than before.

     

    thanks in advance

  • October 11, 2006 at 10:43 pm

    #665370

    SELECT RecID

    FROM Customer A

    WHERE TypeID = 'A'

    AND EXISTS(select 1 from Customer B

    where B.CustID = A.CustID AND B.TypeID <> A.TypeId)

     

    _____________
    Code for TallyGenerator

  • October 11, 2006 at 11:04 pm

    #665377

    Thanks a lot. Its working fine.

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

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