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

  • From: "Charles Schultz" <sacrophyte@xxxxxxxxx>
  • To: Jacques.Kilchoer@xxxxxxxxx
  • Date: Thu, 30 Nov 2006 13:31:28 -0600

Do you really need the rank? If not, you can use these analytics with no
inline-query:

SQL > select distinct id, min(value) over (partition by id) smallest,
max(value) over (partition by id) largest from zzz order by 2,3;

However, if you absolutely need the rank, I am having trouble coming up with
a simple solution not using a subquery - have to wrap the whole thing and
grab rownum like you do. You could use something exotic like a hierarchy,
probably, but that rather defeats the purpose of making this simple.

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





--
Charles Schultz

Other related posts:

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