troubleshooting Question

How to find the duplicate records in oracle SQL?

Avatar of Steve Berger
Steve BergerFlag for United States of America asked on
Oracle Database
36 Comments1 Solution2157 ViewsLast Modified:
Hi,
   I have table which contains huge number of data. Attached is the sample data for my scenario. If you see the sample data file,  the column name ( NAME, FACTOR_ATTR_CONTEXT, FACTOR_ATTRIBUTE, ATTR_VALUE, START_DATE_ACTIVE, END_DATE_ACTIVE) which is contains the same data. It is only differ in ADJUSTMENT_FACTOR. Thing is, all the columns except ADJUSTMENT_FACTOR should not repeat again, if the ADJUSTMENT_FACTOR value is different also. See the LINE_INDEX 1,2 data. I want to get the combination of column data which are not present again with different ADJUSTMENT_FACTOR value. If it comes again both the combination are duplicate. For identifying the combination we can use the LINE_INDEX column. For example LINE_INDEX 1 is one combination, 2 is another combination like that. Another thing is the ADJUSTMENT_FACTOR value is same for different combination also. If it is there, that is unique data. See the line_index value 3. Here the ADJUSTMENT_FACTOR value is same 0.4. But it is differ in ATTR_VALUE. So this is unique record. I want to write a SQL Query to achieve that. I tried so many times. Below is my query. can any one share with your thoughts?

Thanks
SELECT a.* FROM 
    FACTOR_STG_SAM a
WHERE 
   NOT EXISTS (SELECT 
                    'x'
                FROM 
                    FACTOR_STG_SAM b
                WHERE 
                   a.NAME = b.NAME
                    AND a.FACTOR_ATTR_CONTEXT = b.FACTOR_ATTR_CONTEXT
                    AND a.FACTOR_ATTRIBUTE = b.FACTOR_ATTRIBUTE
                    AND a.ATTR_VALUE = b.ATTR_VALUE
                    AND NVL(a.ADJUSTMENT_FACTOR,0) <> NVL(b.ADJUSTMENT_FACTOR,0)
                    AND a.START_DATE_ACTIVE = b.START_DATE_ACTIVE
                    AND a.END_DATE_ACTIVE = b.END_DATE_ACTIVE
                );
sample-date.xls
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 36 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 36 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros