Thread: Duplicate Recs

    #1
  1. No Profile Picture
    Junior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Nov 2003
    Posts
    2
    Rep Power
    0

    Cool Duplicate Recs


    I AM HAVING A TIME DISPLAYING ALL FIELDS OF RECORDS AFTER FINDING DUPLICATES. I THOUGHT SOMETHING LIKE THIS WOULD WORK BUT... NOT!

    SELECT EMP_CODE, EMP_NAME, EMP_STATUS, COMPANY, OFFICE_CODE, OFFICE_TYPE
    FROM TBEMP
    WHERE SOCIAL_SEC_NUM IN (SELECT SOCIAL_SEC_NUM FROM TBEMP GROUP BY SOCIAL_SEC_NUM HAVING COUNT(SOCIAL_SEC_NUM) > 2);


    ANY INPUT?
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Oct 2003
    Posts
    60
    Rep Power
    11
    I believe you need an order by in your inner select... The GROUP BY will make your COUNT do what you want, but you have to ORDER the search results for GROUP BY.

    Try adding "ORDER BY SOCIAL_SEC_NUM ASC". Test your inner query first and verify manully that a few of it's returnd SSN values really are dupes.

    Then use your inner query with your outer query.

    -c
  4. #3
  5. No Profile Picture
    Senior Member
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Location
    Canada
    Posts
    305
    Rep Power
    11
    First: You cannnot use an ORDER BY clause in subquery defined in WHERE clause, but you do use ORDER BY clause in subquery defined in FROM clause.
    Second: You should have been used count(social_sec_num)>1 in HAVING clause of your subquery to find the duplicate records, instead of using count(socila-sec_num)>2 ????
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Sep 2003
    Posts
    68
    Rep Power
    11
    In the ideal case, you shouldn't have duplicates. Easier said than done, I guess, but all that it takes is having the proper primary key set up.

    Assuming that per each distinct SOCIAL_SEC_NUM, the duplicate rows are identical, then the following should work:

    SELECT
    distinct SOCIAL_SEC_NUM, EMP_CODE, EMP_NAME, EMP_STATUS, COMPANY, OFFICE_CODE, OFFICE_TYPE
    FROM
    TBEMP


    BTW, getting the duplicates is done via:

    SELECT
    SOCIAL_SEC_NUM, COUNT(SOCIAL_SEC_NUM)
    FROM
    TBEMP
    GROUP BY
    SOCIAL_SEC_NUM
    HAVING
    COUNT(SOCIAL_SEC_NUM) > 1);

    Regards,
    Dan

IMN logo majestic logo threadwatch logo seochat tools logo