Re: I still have trouble wrapping my head around these analytical function
- From: "Jonathan Lewis" <jonathan@xxxxxxxxxxxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Fri, 1 Dec 2006 20:23:01 -0000
I don't think you can get away from an inline view, as
analytics will not allow you to reduce the number of
rows unless you wrap a query in an inline view and
then have the outer query compare with an analytic
result.
Here's an analytic short at your requirement - it's
even worse than your original :-(
select
id, smallest, largest, rownum
from (
select
id, smallest, largest
from (
select
id,
min(value) over (partition by id) smallest,
max(value) over (partition by id) largest,
row_number() over(partition by id order by value) rn
from zzz
)
where
rn = 1
order by
smallest, largest, id
)
/
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Subject: > Date: Thu, 30 Nov 2006 11:03:55 -0800
From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
I SMALLEST LARGEST RANK
- --------- --------- ---------
x 10 19 1
b 20 20 2
j 20 30 3
c 30 32 4
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Re: I still have trouble wrapping my head around these analytical function
