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_at_quest.com>
>
>
> 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