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

select distinct id
  , min(value) over (partition by id) min_value
  , max(value) over (partition by id) max_value
from zzz
order by 2,3


On 11/30/06, Jacques Kilchoer <Jacques.Kilchoer@xxxxxxxxx> 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





--
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist

Other related posts: