|
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
![]() |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
|
|
|
Stop making mediocre tutorials.The best tutorials are video! Camtasia Studio makes it easy to create engaging, buzz-building screen videos at any size, in any popular format. Download the free trial!
|
|
#1
|
|||
|
|||
|
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
|
|||
|
|||
|
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 |
|
#3
|
|||
|
|||
|
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 ???? |
|
#4
|
|||
|
|||
|
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 |
![]() |
| Viewing: Dev Shed Forums > Databases > Oracle Development > Duplicate Recs |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
|
|
|