Re: Top N Results From Aggregate Values in A Select

From: Tim Marshall <TIMMY!_at_PurplePandaChasers.Moertherium>
Date: Thu, 08 Dec 2005 13:39:27 -0330
Message-ID: <dn9pc8$cc4$1@coranto.ucs.mun.ca>

SL wrote:

> You can also RTFM for Rank analytic function.
> Otherwise, select from your select statement (inline view) ordering by
> Request desc and selecting the N first rows using rownum.
> And, as Sybrand Bakker pointed it, look up the NG for Top N Queries :)

Thanks for the response. So using the solution you suggested (other than reading about Rank functions), the statement which following works really nicely.

But would the rank function do this more efficiently? I am having a great deal of difficulty understanding the whole analytical processing aspect of Oracle.

select Request, CN_NAME from (

select Request, CN_NAME from (

select
count(*) Request,
CN_NAME
from
tma.f_workorder, tma.f_clients
where

WO_TYPE_CODE IN ('1') and
wo_request_date >= to_date('1-APR-2004') and
wo_request_date <= to_date('31-MAR-2005') and
wo_cn_fk = cn_pk(+)

group by CN_NAME

)

order by 1 desc

)

where rownum < 11

-- 
Tim    http://www.ucs.mun.ca/~tmarshal/
^o<
/#) "Burp-beep, burp-beep, burp-beep?" - Quaker Jake
/^^ "Whatcha doin?" - Ditto  "TIM-MAY!!" - Me

Received on Thu Dec 08 2005 - 11:09:27 CST