RE: I still have trouble wrapping my head around these analytical functions (should be simple?)
- From: "Jacques Kilchoer" <Jacques.Kilchoer@xxxxxxxxx>
- To: "oracle-l" <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 30 Nov 2006 11:43:20 -0800
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@xxxxxxxxx]
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@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:
