Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> I still have trouble wrapping my head around these analytical functions (should be simple?)

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US