Home » SQL & PL/SQL » SQL & PL/SQL » Comparing values of two columns in same table
Comparing values of two columns in same table [message #264485] Mon, 03 September 2007 09:11 Go to next message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

Hi friends,

I would like to have results as below. either from sql or plsql is fine. the below example i cant use, since the table i am reading is big one.

Created table som123 with columns C1 and C2. C1 for bill to cust and C2 for ship to

SELECT *
FROM som123
C1 C2
------------------------------------------
1 1
1 2
1 3
2 1
2 2
2 3
3 4
3 5

SELECT *
FROM som123
WHERE c1=c2
C1 C2
------------------------------------------
1 1
2 2

SELECT DISTINCT c1
FROM som123
WHERE c1 NOT IN (Select c1
FROM som123
WHERE c1=c2)

C1
--------------------------------------------
3

i would like to have '3' as output.

Pls suggest me the best way that we can get it.
Re: Comparing values of two columns in same table [message #264496 is a reply to message #264485] Mon, 03 September 2007 09:56 Go to previous messageGo to next message
Soumen Kamilya
Messages: 128
Registered: August 2007
Location: Kolkata
Senior Member

You can try with that
with data as
(select 1 col1,1 col2 from dual
union all
select 1 col1,2 col2 from dual
union all
select 1 col1,3 col2 from dual
union all
select 2 col1,1 col2 from dual
union all
select 2 col1,2 col2 from dual
union all
select 2 col1,3 col2 from dual
union all
select 3 col1,4 col2 from dual
union all
select 3 col1,5 col2 from dual)
select col1 from data
minus
select col1 from data WHERE col1=col2


Cheers
Soumen
Re: Comparing values of two columns in same table [message #264507 is a reply to message #264496] Mon, 03 September 2007 10:21 Go to previous message
saikumar.mvvr
Messages: 18
Registered: September 2007
Location: India
Junior Member

Thx Soumen, it worked !
Previous Topic: update column problem
Next Topic: ORA-01536
Goto Forum:
  


Current Time: Fri Dec 09 00:03:42 CST 2016

Total time taken to generate the page: 0.09046 seconds