RE: SQL question

  • From: Stephane Faroult <sfaroult@xxxxxxxxxxxx>
  • To: "'Stephane Faroult '" <sfaroult@xxxxxxxxxxxx>, "''oracle-l@xxxxxxxxxxxxx ''" <oracle-l@xxxxxxxxxxxxx>, "''geraldine_2@xxxxxxxxxxx ''" <geraldine_2@xxxxxxxxxxx>, Freeman Robert - IL <FREEMANR@xxxxxxxx>
  • Date: Fri, 3 Dec 2004 18:32:43 +0100

 Many people have made the right diagnosis (duplicates), but I believe the
solution to be wrong (distinct).
I typed too fast and it should have been :
select least(a, b) a, greatest(a, b) b
from mytable
where a <= b


Regards, 

Stephane Faroult 

RoughSea Ltd 
http://www.roughsea.com 


On Fri, 3 Dec 2004 11:10 , Freeman Robert - IL <FREEMANR@xxxxxxxx> sent:

Think you need a distinct in there, yes?

select distinct least(a, b) a, greatest(a, b) b 
from my_table 
where least(a, b) <= greatest(a, b);

Otherwise you get dupes, which he didn't have in his sample output.

RF



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx[1]
To: oracle-l@xxxxxxxxxxxxx[2]; geraldine_2@xxxxxxxxxxx[3]
Sent: 12/3/2004 11:09 AM
Subject: Re: SQL question


select least(a, b) a, 

greatest(a, b) b 

from my_table 

where least(a, b) <= greatest(a, b) 

Assuming of course that the table isn't in the multi-million range. 



Regards, 

Stephane Faroult 

RoughSea Ltd 
http://www.roughsea.com[4] 


On Fri, 03 Dec 2004 16:37 , geraldine_2@xxxxxxxxxxx[5] sent:

Hi,
I have the following table below 

SQL> select * FRom mytable;
A B
---------- ----------
1 2
3 4
2 1
5 6
4 3

5 rows selected.

and I like to get the following output:

A B
---------- ----------
1 2
3 4
5 6


basically (1,2) is the same as (2,1) and I would just like to display
any of
those combination just once.

Not sure how I can write a SQL to extract the data. Can someone help.

TIA.

Geraldine
--
//www.freelists.org/webpage/oracle-l[1]



--- Links ---
1
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org[6]%2Fwebpage%2Fora
cle-l
--
//www.freelists.org/webpage/oracle-l[7]
--
//www.freelists.org/webpage/oracle-l[8]



--- Links ---
   1 javascript:parent.opencompose('oracle-l-bounce@xxxxxxxxxxxxx','','','')
   2 javascript:parent.opencompose('oracle-l@xxxxxxxxxxxxx','','','')
   3 javascript:parent.opencompose('geraldine_2@xxxxxxxxxxx','','','')
   4 modules/refer.pl?redirect=http%3A%2F%2Fwww.roughsea.com
   5 javascript:parent.opencompose('geraldine_2@xxxxxxxxxxx','','','')
   6 modules/refer.pl?redirect=//www.freelists.org
   7 
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
   8 
modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org%2Fwebpage%2Foracle-l
--
//www.freelists.org/webpage/oracle-l

Other related posts: