Re: I still have trouble wrapping my head around these analytical function


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


--
http://www.freelists.org/webpage/oracle-l


Other related posts:

  • » Re: I still have trouble wrapping my head around these analytical function