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?
SELECT a.* FROM FACTOR_STG_SAM aWHERE 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 );
You are missing 1 thing from your sub query. There is no way to distinguish the original row you want to keep. This is a way to do it, but by no means the only way.
Open in new window