Home » SQL & PL/SQL » SQL & PL/SQL » remove duplicate data in other columns (oracle 10g on windows 2003)
remove duplicate data in other columns [message #421833] Thu, 10 September 2009 23:16 Go to next message
kang
Messages: 89
Registered: November 2007
Member
with t as (
select 'a' key1, 'x' val1, 'b' key2, 'r' val2 from dual union
select 'b' key1, 'r' val1, 'a' key2, 'x' val2 from dual union
select 'c' key1, 'd' val1, 'x' key2, 'r' val2 from dual union
select 'x' key1, 'r' val1, 'c' key2, 'd' val2 from dual
) select * from t


in the query above
('a','x') in key1,val1is in key2,val2
so I want one row.
so the desire result I want is
a x b r
x r c d

[Updated on: Thu, 10 September 2009 23:47]

Report message to a moderator

Re: remove duplicate data in other columns [message #421844 is a reply to message #421833] Fri, 11 September 2009 00:57 Go to previous messageGo to next message
sen_sam86
Messages: 33
Registered: August 2009
Location: Chennai
Member
with t as (
select 'a' key1, 'x' val1, 'b' key2, 'r' val2 from dual minus
select 'b' key1, 'r' val1, 'a' key2, 'x' val2 from dual MINUS
select 'c' key1, 'd' val1, 'x' key2, 'r' val2 from dual union
select 'x' key1, 'r' val1, 'c' key2, 'd' val2 from dual
) select * from t

OUTPUT

KEY1 VAL1 KEY2 VAL2 
---- ---- ---- ---- 
a    x    b    r    
x    r    c    d 

Are you expecting this...!

Re: remove duplicate data in other columns [message #421848 is a reply to message #421844] Fri, 11 September 2009 02:09 Go to previous messageGo to next message
kang
Messages: 89
Registered: November 2007
Member
the t table should be intact.
Re: remove duplicate data in other columns [message #421849 is a reply to message #421848] Fri, 11 September 2009 02:24 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Why do you keep "a x b r" and not "b r a x"?

Regards
Michel
Re: remove duplicate data in other columns [message #421850 is a reply to message #421849] Fri, 11 September 2009 02:47 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
We've had a post like this before - If I understand him correctly he wants to get all distinct pairs of values - he doesn't care which order they're in. So I don't think it has to be A X B R - B R A X would do just as well.
Re: remove duplicate data in other columns [message #421851 is a reply to message #421850] Fri, 11 September 2009 02:53 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Assuming I'm correct, this query should do the job:
with t as (
select 'a' key1, 'x' val1, 'b' key2, 'r' val2 from dual union
select 'b' key1, 'r' val1, 'a' key2, 'x' val2 from dual union
select 'c' key1, 'd' val1, 'x' key2, 'r' val2 from dual union
select 'x' key1, 'r' val1, 'c' key2, 'd' val2 from dual
) select distinct
         case when key1 <  key2 then key1 else key2 end key1
        ,case when key1 <  key2 then val1 else val2 end val1
        ,case when key1 >= key2 then key1 else key2 end key2
        ,case when key1 >= key2 then val1 else val2 end val2
from t;
Re: remove duplicate data in other columns [message #421853 is a reply to message #421850] Fri, 11 September 2009 02:58 Go to previous messageGo to next message
Michel Cadot
Messages: 64102
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
SQL> with 
  2    t as (
  3     select 'a' key1, 'x' val1, 'b' key2, 'r' val2 from dual union
  4     select 'b' key1, 'r' val1, 'a' key2, 'x' val2 from dual union
  5     select 'c' key1, 'd' val1, 'x' key2, 'r' val2 from dual union
  6     select 'x' key1, 'r' val1, 'c' key2, 'd' val2 from dual
  7    ),
  8    data as (
  9      select distinct
 10             least(key1||'/'||val1,key2||'/'||val2) l,
 11             greatest(key1||'/'||val1,key2||'/'||val2) g
 12      from t
 13    )
 14  select substr(l,1,instr(l,'/')-1) key1,
 15         substr(l,instr(l,'/')+1) val1,
 16         substr(g,1,instr(g,'/')-1) key2,
 17         substr(g,instr(g,'/')+1) val2
 18  from data
 19  /
KEY VAL KEY VAL
--- --- --- ---
a   x   b   r
c   d   x   r

2 rows selected.

Regards
Michel

[Updated on: Fri, 11 September 2009 03:02]

Report message to a moderator

Re: remove duplicate data in other columns [message #421943 is a reply to message #421853] Sat, 12 September 2009 00:42 Go to previous message
kang
Messages: 89
Registered: November 2007
Member
Thanks all you.
Previous Topic: simple select command
Next Topic: Get name list files in folder
Goto Forum:
  


Current Time: Fri Dec 02 14:31:02 CST 2016

Total time taken to generate the page: 0.18423 seconds