RE: finding the smallest average with an analytic function - is i

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Mon, 19 May 2003 12:12:18 -0800
Message-ID: <F001.0059CF07.20030519121218@fatcity.com>

select name, avg_value from (
select name, avg(value) avg_value, rank( ) over ( order by avg(value)) rank from x
group by name)
where rank = 1;

select * from (select name,avg(value)
from x
group by name
order by 2 asc) where rownum = 1  

Waleed

-----Original Message-----
Sent: Monday, May 19, 2003 3:37 PM
To: Multiple recipients of list ORACLE-L

I want to find the name having the smallest average value. Is it possible to do it using analytic functions? Is it possible to do it using analytic functions WITHOUT a sub-select?

create table x (name varchar2 (4), value number) ;

insert into x (name, value) values ('A', 2) ; 
insert into x (name, value) values ('A', 3) ; 
insert into x (name, value) values ('A', 4) ; 
insert into x (name, value) values ('B', 1) ; 
insert into x (name, value) values ('B', 2) ; 
insert into x (name, value) values ('B', 3) ; 
insert into x (name, value) values ('C', 3) ; 
insert into x (name, value) values ('C', 4) ; 
insert into x (name, value) values ('C', 5) ; 
insert into x (name, value) values ('C', 6) ; 

• is it possible to rewrite the query below using analytic functions? select name, avg (value) from x group by name having avg (value) = (select min (avg (value)) from x group by name) ;

Expected output:
NAME AVG(VALUE)
---- ----------
B 2

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Khedr, Waleed
  INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Mon May 19 2003 - 15:12:18 CDT