Query needed [message #194027] |
Wed, 20 September 2006 08:29 |
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 |
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
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 |
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 |
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..
|
|
|
|