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

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Thu, 30 Nov 2006 11:03:55 -0800
Message-ID: <22EC50E60931494FA666A8CF8260C45B5D9B22@ALVMBXW05.prod.quest.corp>

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

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

Received on Thu Nov 30 2006 - 13:03:55 CST