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

Unfortunately I really do want the rank. As far as your analytics example vs. using min() and max () and group by, I will compare to see which is more efficient, but I would imagine that they are the same.

De : Charles Schultz [mailto:sacrophyte_at_gmail.com] Envoyé : jeudi, 30. novembre 2006 11:31
À : Jacques Kilchoer
Cc : oracle-l
Objet : Re: I still have trouble wrapping my head around these analytical functions (should be simple?)

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

--

Charles Schultz

--

http://www.freelists.org/webpage/oracle-l Received on Thu Nov 30 2006 - 13:43:20 CST