Oracle FAQ Your Portal to the Oracle Knowledge Grid
 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US

Home -> Community -> Mailing Lists -> Oracle-L -> RE: SQL question

# RE: SQL question

From: Stephane Faroult <sfaroult_at_roughsea.com>
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_at_tusc.com> 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_at_freelists.org[1]
To: oracle-l_at_freelists.org[2]; geraldine_2_at_comcast.net[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_at_comcast.net[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
--

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

• Links --- 1 modules/refer.pl?redirect=http%3A%2F%2Fwww.freelists.org[6]%2Fwebpage%2Fora cle-l
--
http://www.freelists.org/webpage/oracle-l[7]
--
http://www.freelists.org/webpage/oracle-l[8]
• Links --- 1 javascript:parent.opencompose('oracle-l-bounce_at_freelists.org','','','') 2 javascript:parent.opencompose('oracle-l_at_freelists.org','','','') 3 javascript:parent.opencompose('geraldine_2_at_comcast.net','','','') 4 modules/refer.pl?redirect=http%3A%2F%2Fwww.roughsea.com 5 javascript:parent.opencompose('geraldine_2_at_comcast.net','','','') 6 modules/refer.pl?redirect=http://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
--
http://www.freelists.org/webpage/oracle-l
Received on Fri Dec 03 2004 - 11:17:47 CST

Original text of this message

 HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US