Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2003
    Posts
    14

    Unanswered: How to compare 2 records in the same table?

    Dear Friends,
    Could anyone let me know how to search for duplicate recorsd in the same table?
    I have 20 attributes in the table and have the first 2 attributes
    ID1,ID2-the combination of which is unique.
    How to write a query to retrieve the duplicate records in the same table(It should compare the remaning 18 attributes and retrieve if they all match).Is this something which can be done in SQL?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1

    Re: How to compare 2 records in the same table?

    Originally posted by mark_tim1999
    Dear Friends,
    Could anyone let me know how to search for duplicate recorsd in the same table?
    I have 20 attributes in the table and have the first 2 attributes
    ID1,ID2-the combination of which is unique.
    How to write a query to retrieve the duplicate records in the same table(It should compare the remaning 18 attributes and retrieve if they all match).Is this something which can be done in SQL?
    The following will retrieve all records that have duplicate ID1 and ID2:

    SELECT ID1, ID2, ..., COUNT(1) FROM table_name
    GROUP BY ID1, ID2
    HAVING COUNT(1) > 1;

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: How to compare 2 records in the same table?

    N_I's query gives the count ,

    If you want the actual rows, you can use a CTE using the same query as sample

    with temp as
    (
    SELECT ID1, ID2, ..., COUNT(1) FROM table_name
    GROUP BY ID1, ID2
    HAVING COUNT(1) > 1
    )
    select * from table_name where
    (id1,id2....) in (select id1,id2 .... from temp
    )

    HTH

    Sathyaram
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  4. #4
    Join Date
    Dec 2003
    Posts
    14
    Thanks for the response....
    But I dont have duplicate ID1,ID2...This combinaton is unique

    Imagine the sample records

    ID1 ID2 NAME SSN BIRTHDATE
    100 1 MARK 999-99-999 1980-01-01
    100 2 MARK 999-99-999 1980-01-01
    200 2 CHRIS 888-88-8888 1990-01-01

    I am looking for a query which compares the two records
    (100,1) & (100,2) for the NAME,SSN,BIRTHDATE(LIKEWISE I have 20 other columns in this table) and gives the count of records if all the attributes match(apart from ID1,ID2)....Hope my question was clear

  5. #5
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Originally posted by mark_tim1999
    Thanks for the response....
    But I dont have duplicate ID1,ID2...This combinaton is unique

    Imagine the sample records

    ID1 ID2 NAME SSN BIRTHDATE
    100 1 MARK 999-99-999 1980-01-01
    100 2 MARK 999-99-999 1980-01-01
    200 2 CHRIS 888-88-8888 1990-01-01

    I am looking for a query which compares the two records
    (100,1) & (100,2) for the NAME,SSN,BIRTHDATE(LIKEWISE I have 20 other columns in this table) and gives the count of records if all the attributes match(apart from ID1,ID2)....Hope my question was clear

    ...
    GROUP BY NAME, SSN, BIRTHDATE
    ...

  6. #6
    Join Date
    May 2003
    Location
    San Juan, PR
    Posts
    18
    Try this statement:

    SELECT A.ID1, A.ID2
    FROM TBL A
    WHERE 1 < (SELECT COUNT(*) FROM TBL B
    WHERE B.FLD1 = A.FLD1
    AND B.FLD2 = A.FLD2
    AND B.FLD3 = A.FLD3
    AND ... )

    Regards, ... Aloz

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •