Home » SQL & PL/SQL » SQL & PL/SQL » outer join / normal select / analytical function
outer join / normal select / analytical function [message #385655] Tue, 10 February 2009 07:24 Go to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi Guys,

Just need some hints, im aware of analytic functions where we can select records based on partition clause.

Basically if i have the following set of records

col1 | col2 | col3 | col4
-------------------
1, abc, A, 1-jan-08
1, abc, E, 2-jan-08
1, abc, K, 3-jan-08
2, abc, E, 2-jan-08
2, abc, K, 3-jan-08

How do i actually extract those records which have col3 = A (if exists), otherwise just extract any first record order by col4, partition by col1
Meaning the output will be


col1 | col2 | col3 | col4
-------------------
1, abc, A, 1-jan-08
2, abc, E, 2-jan-08

i plan to build 2 queries with union all clause,

1) with all records which has col3 = A
2) with all records which has col3 <> A and here i will have a nested query to select first value , partition order by col4.

But i cant seem to figure out how do i write the nested query

select * from tab1 where col3 = 'A'
union all
select a.* from tab1 where col3 <> 'A' and col1 no in
( select col1 from tab1 where col3 = 'A')


Is there any other approach
Re: outer join / normal select / analytical function [message #385661 is a reply to message #385655] Tue, 10 February 2009 07:48 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Is there any other approach

Yes, use ROW_NUMBER.

Post a Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: outer join / normal select / analytical function [message #385668 is a reply to message #385661] Tue, 10 February 2009 08:20 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Below is my test-case

create table  tab1(
 col1 number,
 col2 varchar2(3),
 col3 varchar2(15))



insert statements

insert into tab1 values(1,'abc','Asset');
insert into tab1 values(1,'abc','Expense');
insert into tab1 values(1,'abc','Equity');
insert into tab1 values(1,'abc','Kouros');
insert into tab1 values(2,'abc','Equity');
insert into tab1 values(2,'abc','Kouros');
commit;


So for each col1, im trying to extract only those records which have col3 = 'Expense', if that particular col1 does not have asset, i want to extract any other next record ordered by col3

this is what im trying to get out of the query


Results

col1 | col2 | col3
-----------------------
1 | abc | Expense
2 | abc | Equity



Below is the query which is failing, the second portion of this query, im not sure how to select only the first row using row_number as you hihglighted

select * from tab1 where col3 = 'Expense'
union all
select a.*, row_number() over (partition by col1 order by col3) as id 
from tab1 a 
where col3 <> 'Expense' 
and col1 not in (select col1 from tab1 where col3 = 'Expense')

[Updated on: Tue, 10 February 2009 08:23] by Moderator

Report message to a moderator

Re: outer join / normal select / analytical function [message #385669 is a reply to message #385668] Tue, 10 February 2009 08:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your query (not just use code tags).

Clue:
SQL> select * from tab1
  2  order by col1, decode(col3,'Expense',' ',col3)
  3  /
      COL1 COL COL3
---------- --- ---------------
         1 abc Expense
         1 abc Asset
         1 abc Equity
         1 abc Kouros
         2 abc Equity
         2 abc Kouros

6 rows selected.


Regards
Michel
Re: outer join / normal select / analytical function [message #385672 is a reply to message #385669] Tue, 10 February 2009 08:40 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
Hi Michel,

Looks like, you have a very powerful mind,

I just cant imagine how you can think of such a query.

I feel so close to the answer but yet i cant figure it out.

The following code doesnt seem to give me all rows with row_number=1

select a.* , row_number() OVER 
(partition by col1 ORDER BY a.col1, decode(a.col3,'Expense',' ',col3)) R from tab1 a
WHERE R = 1

[Updated on: Tue, 10 February 2009 08:48]

Report message to a moderator

Re: outer join / normal select / analytical function [message #385677 is a reply to message #385672] Tue, 10 February 2009 08:56 Go to previous messageGo to next message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
woooooooow..

thank you michel, i got it, god bless ya, learn somethng new today, now im able to sleep peacefully


select * from 
(select col1,col2,col3 , 
       row_number() OVER (partition by col1 ORDER BY col1, decode(col3,'Expense',' ',col3)) R 
 from tab1)
WHERE R = 1;

[Updated on: Tue, 10 February 2009 09:04] by Moderator

Report message to a moderator

Re: outer join / normal select / analytical function [message #385680 is a reply to message #385677] Tue, 10 February 2009 09:04 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You don't need to order by col1 after partitioning by col1 because each element of a partition have the same col1.

Regards
Michel
Re: outer join / normal select / analytical function [message #385684 is a reply to message #385680] Tue, 10 February 2009 09:35 Go to previous message
ajitpal.s
Messages: 204
Registered: November 2006
Senior Member
I see, noted, thanks michel
Previous Topic: update records using cursor (merged 3)
Next Topic: Outer join logic
Goto Forum:
  


Current Time: Sat Dec 03 05:41:29 CST 2016

Total time taken to generate the page: 0.07869 seconds