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

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Thu, 30 Nov 2006 21:50:27 +0200
Message-ID: <6e49b6d00611301150p7bc596eejb5c024fb543ec0b6@mail.gmail.com>

enhancing Jareds' example with rank column

select id, min_value, max_value, row_number() over (order by min_value, max_value) rank from (
  select distinct id

     , min(value) over (partition by id) min_value
     , max(value) over (partition by id) max_value

  from zzz
)
order by rank

actually you haven't clearly defined for example what you'd like to get if min_value and max_value are the same. Depending on that you can use rank or dense_rank instead of row_number.

Gints Plivna
http://www.gplivna.eu

2006/11/30, Jared Still <jkstill_at_gmail.com>:
>
> 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

Received on Thu Nov 30 2006 - 13:50:27 CST