Home » SQL & PL/SQL » SQL & PL/SQL » Query needed
Query needed [message #194027] Wed, 20 September 2006 08:29 Go to next message
maxboom123
Messages: 9
Registered: August 2006
Junior Member
Hi All,
I'm to new to oracle and need a query based on the following data.

I have two tables T1 and T2. Data in the tables are as follows.

T1
--

A B
--- ---
10 Pa
10 Pb
10 Pc
20 Pb
20 Pc
30 Pa
30 Pc
40 Pc

T2
----
A
---
10
20
30
40

I want to join T1 & T2 (over column A) and the result should as follows

A B
--- --
10 Pa
20 Pb (selects Pb as Pa not available)
30 Pa
40 Pc (selects Pc as Pa & Pb not available)

ie, value from the column B will be taken in the order(pa, pb, pc)

Can some one please help me in creating the query?

Regards,
MX
Re: Query needed [message #194029 is a reply to message #194027] Wed, 20 September 2006 08:40 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, by the time you've read up everything you need to understand these 3 solutions, you should be substantially ledd new to Oracle Cool

create table t1 (A  number, B varchar2(10));

insert into t1 values (10 ,'Pa');
insert into t1 values (10 ,'Pb');
insert into t1 values (10 ,'Pc');
insert into t1 values (20 ,'Pb');
insert into t1 values (20 ,'Pc');
insert into t1 values (30 ,'Pa');
insert into t1 values (30 ,'Pc');
insert into t1 values (40 ,'Pc');

The least high tech approach (will also work on 9i)
SQL> select a,decode(min(decode(b,'Pa',1,'Pb',2,'Pc',3)),1,'Pa',2,'Pb',3,'Pc') B
  2  from   t1
  3  group by a;

         A B
---------- --
        10 Pa
        20 Pb
        30 Pa
        40 Pc

The Analytic function approach
SQL> select distinct a, first_value(b) over (partition by a order by decode(b,'Pa',1,'Pb',2,'Pc',3))
  2  from   t1;

         A FIRST_VALU
---------- ----------
        10 Pa
        20 Pb
        30 Pa
        40 Pc

The Obscure corner of aggregate function that can make experienced developers go 'WTF'
SQL> select a, min(b) keep (dense_rank first order by decode(b,'Pa',1,'Pb',2,'Pc',3))
  2  from   t1
  3  group by a;

         A MIN(B)KEEP
---------- ----------
        10 Pa
        20 Pb
        30 Pa
        40 Pc
Re: Query needed [message #194134 is a reply to message #194029] Wed, 20 September 2006 22:52 Go to previous messageGo to next message
ehegagoka
Messages: 493
Registered: July 2005
Senior Member
hi!
is this also correct?


SQL> select a, min(b)
  2  from t3
  3  group by a
  4  order by a
  5  /

         A MIN(B)
---------- ----------
        10 Pa
        20 Pb
        30 Pa
        40 Pc

SQL> 


Re: Query needed [message #194170 is a reply to message #194134] Thu, 21 September 2006 01:50 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It gives the right result, so it's correct in a very important sense.
It just relies on the order in which the values in column B are wanted being the same as their order as strings..
Re: Query needed [message #194285 is a reply to message #194027] Thu, 21 September 2006 07:08 Go to previous message
maxboom123
Messages: 9
Registered: August 2006
Junior Member
Thanks every one
Previous Topic: count of each data for column
Next Topic: Challenge and A challenging question (merged 2 cross-posts)
Goto Forum:
  


Current Time: Thu Dec 12 03:35:52 CST 2024