Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

Troubleshooting
Research
Professional Opinions
Ask a Question
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

troubleshooting Question

How to find the duplicate records in oracle SQL?

Avatar of Suriyaraj_Sudalaiappan
Suriyaraj_SudalaiappanFlag 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