CODEWITHSUNDEEP
sqloracle-database
user3656661
user3656661

Reputation: 1

PL SQL How to eliminate duplication with two columns in a table and

There is a similar question already asked, but I have couple of differences

I have this table>>

No1 No2  Data       Customer
1   2   01.01.2013  120000
2   1   01.02.2013  100000
3   4   03.06.2011  150000
4   3   05.09.2010  160000
5   6   15.02.2013  110000
6   5   29.06.2014  190000
1   6   19.05.2013  100000
6   1   04.08.2013  120000

9 2 01.07.2011 100000

What I want is to eliminate rows that have the same value but in the other field. For me No1=1 No2=2 and No1=2 No2=1 is the same thing. So when this sort of combination ocures it should give back just one row.

At the end I want this as result>>

No1 No2  Data      Customer
1   2   01.01.2013  120000
3   4   03.06.2011  150000
5   6   15.02.2013  110000
1   6   19.05.2013  100000

9 2 01.07.2011 100000

I have found a solution but just for the first two columns

select distinct least(no1, no2), greatest(no1, no2)
from t

but I need the 'Data' and 'Customer' columns also

if I try with 

select distinct least(no1, no2), greatest(no1, no2), max(Data), max(Customer)
from t

it will give me the maximal from Data and Customer but I want the values to correspond/match the row..

My real table is actually select from lots of tables with subqueries, so getting a result is time consuming thing, that's why I want the simplest and fastest solution.

Any hint/advice is appreciated

thanks IV

--EDIT--/6 Hours later/

I forgot to mention that I have rows which are single without combination like the one I just puted in the original table(the last one outside the table :) )

so I found hear one answer that helped me do this

select t1.*  
from MyTable t1  
left outer join MyTable t2 on t1.No1 = t2.No2 and t1.No2 = t2.No1  
where t2.No2 is null   --- this will give me the singles  
    or t1.No1 <= t2.No1  --- and this will give me one row where the combination occures

thanks a lot all of you IV

Upvotes: 0

Views: 58

Answers (3)

Serpiton
Serpiton

Reputation: 3684

You can use MINUS, this will get the last not coupled row

SELECT *
FROM   Table1
WHERE (No1, No2) IN (SELECT No1, No2
                     FROM   Table1
                     MINUS
                     SELECT No2, No1
                     FROM   Table1
                     WHERE  No1 < No2);

SQLFiddle demo

Upvotes: 0

user330315
user330315

Reputation: 

select *
from the_table
where (no1, no2) in (select distinct least(no1, no2), greatest(no1, no2)
                     from the_table);

This would however return duplicates if the combination (no1, no2) is not unique e.g. if there is more than one row with no1=1 and no2=2

Upvotes: 0

Jens Schauder
Jens Schauder

Reputation: 82008

Something like this should work with Oracle. Don't have a db to test, so it will contain syntax errors.

select * from (
    select least(no1, no2), greatest(no1, no2), Data, Customer,
        rank() over (partition by least(no1, no2), greatest(no1, no2)
            order by Customer desc) r
    from t
) where r = 1

Upvotes: 1

Related Questions