Re: Another SQL query help

Hi John,

Here are my two cents:

query 1:
select t1.col2, t2.colx
  from t1, t2
 where t1.col2 = Initcap(substr(t2.colx, 2));

query 2:
select t1.col2, b.colx
  from t1, (select col1, min(colx) colx from t2 group by col1) b
 where t1.col1 = b.col1;

query 3:
select t1.col2, b.colx
  from t1, (select col1, max(colx) colx from t2 group by col1) b
 where t1.col1 = b.col1;

Regards
 
 
Asif Momen
Oracle ACE 
Speaker at Oracle OpenWorld-2010
Member, Editorial Board, Oracle Connect, All India Oracle User Group
Oracle Certified Professional  10g, 9i
Oracle Blogger at – http://momendba.blogspot.com




________________________________
From: john scott <joscott2011.mail_at_gmail.com>
To: oracle-l_at_freelists.org
Sent: Thu, February 24, 2011 9:56:35 PM
Subject: Another SQL query help

I am scrathing my head to write a SQL query to get this information:

I have two tables t1 and t2  with these values

Table t1
======
Col1        Col2
------         ------
1             One
2             Two
3             Three

Table t2
=======
Col1       Colx
------        -------          
1            Xone     
2            Xtwo      
2            Xtwotwo         
3            XThree

I need to select Col2 from Table t1 and Colx from Table t2 with only distinct 
values for Col1.

Here is what I am expecting

t1.Col2           t2. Colx
----------           ----------- 
One               Xone
Two               Xtwo              
Three            XThree

In t2.Colx value Xtwotwo is also acceptable as I need only one distinct value 
from t1.Col2 and don't care for
whatever shows up under t2.Colx.

Any suggestions?

Thanks,
John



      

--
http://www.freelists.org/webpage/oracle-l