finding the smallest average with an analytic function - is it po

From: Jacques Kilchoer <Jacques.Kilchoer_at_quest.com>
Date: Mon, 19 May 2003 11:36:48 -0800
Message-ID: <F001.0059CDA5.20030519113648@fatcity.com>

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: Jacques Kilchoer
  INET: Jacques.Kilchoer_at_quest.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 - 14:36:48 CDT