Re: I still have trouble wrapping my head around these analytical functions (should be simple?)

Do you really need the rank? If not, you can use these analytics with no inline-query:

SQL > select distinct id, min(value) over (partition by id) smallest, max(value) over (partition by id) largest from zzz order by 2,3;

However, if you absolutely need the rank, I am having trouble coming up with a simple solution not using a subquery - have to wrap the whole thing and grab rownum like you do. You could use something exotic like a hierarchy, probably, but that rather defeats the purpose of making this simple.

On 11/30/06, Jacques Kilchoer <Jacques.Kilchoer_at_quest.com> wrote:
>
> Here is my data:
>
> drop table zzz ;
> create table zzz (id varchar2 (1), value number (3)) ;
> insert into zzz (id, value)
> select 'j', 30 from dual
> union all select 'c', 30 from dual
> union all select 'x', 10 from dual
> union all select 'x', 11 from dual
> union all select 'x', 12 from dual
> union all select 'x', 12 from dual
> union all select 'x', 19 from dual
> union all select 'j', 20 from dual
> union all select 'j', 25 from dual
> union all select 'j', 26 from dual
> union all select 'b', 20 from dual
> union all select 'b', 20 from dual
> union all select 'c', 31 from dual
> union all select 'c', 32 from dual ;
> commit ;
>
> I want to get this output. Shouldn't it be possible to write it with
> analytical functions and no inline view?
>
> SQL> select id, smallest, largest, rownum as rank
> 2 from
> 3 (select id, min (value) as smallest, max (value) as largest
> 4 from zzz
> 5 group by id
> 6 order by 2, 3
> 7 ) ;
>
> I SMALLEST LARGEST RANK
> - --------- --------- ---------
> x 10 19 1
> b 20 20 2
> j 20 30 3
> c 30 32 4
>

-- 
Charles Schultz

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