[DARKDEV]

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?

[cconstantine]

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

[shafique]

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 ????

[Dan Drillich]

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