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

  • From: "Gints Plivna" <gints.plivna@xxxxxxxxx>
  • To: jkstill@xxxxxxxxx
  • Date: Thu, 30 Nov 2006 21:50:27 +0200

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@xxxxxxxxx>:


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


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

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 11-2006