Home » SQL & PL/SQL » SQL & PL/SQL » Help with analytical function
Help with analytical function [message #233093] Tue, 24 April 2007 11:08 Go to next message
jinga
Messages: 115
Registered: January 2003
Senior Member


item            mont      tree            cell

100011856	200605	100007330	100001460
100011856	200606	100003539	100000684
100011856	200607	100003539	100000684
100011856	200608	100003539	100000684
100011856	200609	100007330	100001460



Result set to be


item            mont     tree            cell

100011856	200605	100007330	100001460
100011856	200606	100003539	100000684
100011856	200609	100007330	100001460




I wrote the sql like this

SELECT distinct item, tree, cell,min(irm) as mont over(partition by tree,cell order by item_sid,irm)
from test
where item = 100011856



the above sql does not give the third record that i expect int he result set.


could any one help me..



Re: Help with analytical function [message #233097 is a reply to message #233093] Tue, 24 April 2007 11:53 Go to previous messageGo to next message
Michel Cadot
Messages: 64120
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Your initial table does not contain "irm" you use in your query.
You don't say why you get the result you get. I mean what are the rules from the initial table to the result.

Regards
Michel
Re: Help with analytical function [message #233113 is a reply to message #233093] Tue, 24 April 2007 15:09 Go to previous messageGo to next message
jinga
Messages: 115
Registered: January 2003
Senior Member
sorry for 'irm' in the post.I should have said 'mont' instead of irm.

SELECT distinct item, tree, cell,min(mont) as mont over(partition by tree,cell order by item_sid,mont)
from test
where item = 100011856



the rule is : since the item,tree,cell are the same for '200606','200607','200608', i would like to get the min month of these three which is '200606'. In the above example, item,tree_cell combination is same in '200605','200609'. But because '200605','200606','200607' records are in between, i want the result set to be

item            mont     tree            cell

100011856	200605	100007330	100001460
100011856	200606	100003539	100000684
100011856	200609	100007330	100001460



Thanks

Re: Help with analytical function [message #233119 is a reply to message #233093] Tue, 24 April 2007 16:06 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member

SQL> select * from foo;

ITEM MONT TREE CELL
--------------- --------- --------------- ---------------
100011856 01-MAY-06 100007330 100001460
100011856 01-JUN-06 100003539 100000684
100011856 01-JUL-06 100003539 100000684
100011856 01-AUG-06 100003539 100000684
100011856 01-SEP-06 100007330 100001460






 select item, m ,tree,cell
     from (
     select item,to_char(mont,'yyyymm') m ,tree,cell,
    case when lag(item||'-'||tree||'-'||cell) over (order by mont) =
       item||'-'||tree||'-'||cell then null
      else
     row_number() over (order by mont) end  rn
     from foo order by mont
   ) where rn is not null
SQL> /

ITEM M TREE CELL
--------------- ------ --------------- ---------------
100011856 200605 100007330 100001460
100011856 200606 100003539 100000684
100011856 200609 100007330 100001460

Re: Help with analytical function [message #233275 is a reply to message #233093] Wed, 25 April 2007 08:38 Go to previous messageGo to next message
srinivnp
Messages: 136
Registered: January 2006
Location: stlouis MO USA
Senior Member
This is perhaps a tiny bit more efficient.


select item, m ,tree,cell
     from (
     select item,to_char(mont,'yyyymm') m ,tree,cell,
    case when lag(item||'-'||tree||'-'||cell) over (order by mont) =
       item||'-'||tree||'-'||cell then null
      else 1 end  rn
     from foo order by mont
   ) where rn is not null




Srini
Re: Help with analytical function [message #233309 is a reply to message #233093] Wed, 25 April 2007 11:02 Go to previous message
jinga
Messages: 115
Registered: January 2003
Senior Member
Srinivas,

Thanks a lot. I was doing a lag for each of the column and that made my sql really big.

Thanks for the concatnation and case logic.

Ananthi
Previous Topic: REFRESH COMPLETE ON DEMAND materialized view
Next Topic: Comparing dates in different time zones
Goto Forum:
  


Current Time: Tue Dec 06 10:30:19 CST 2016

Total time taken to generate the page: 0.15018 seconds