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_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
>

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

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