Re: TOP N PERCENT?

From: Brian Peasland <peasland_at_edcmail.cr.usgs.gov>
Date: Thu, 18 Nov 1999 14:59:56 GMT
Message-ID: <3834146C.D30FC465@edcmail.cr.usgs.gov>

Since you already know how to return the "Top N" rows, all you need to do is find the total number of rows (select count(*)) and multiply that by your percentage. Use that in "Top N" to get what you want. For instance, if I want to return the top 15% of rows:

DECLARE
  numrows NUMBER;
  percent NUMBER;
BEGIN
  percent := 0.15;

SELECT count(*) INTO numrows FROM table;

numrows := TRUNC(numrows * percent);

SELECT * FROM (SELECT * FROM table ORDER BY col) WHERE ROWNUM < numrows;

END;
/

HTH,
Brian

Jennifer Skripac wrote:
>
> Hi. :)
>
> I'm a pretty wet-behind-the-ears newbie whose
> revisiting the "TOP N" issue. I've done a bit
> of homework over at Deja and have seen some
> basic code ideas that work with the straight
> "Top N" question. (None of which I completely
> understand, but hey. :) )
>
> My question: Is there a way to adjust the
> "Top N" method to show, instead, the Top N
> Percent?? I have a table containing company
> sites and need to do a count of support calls
> logged for each site and query out the top
> 15% of those sites.
>
> Any suggestions would be much appreciated.
>
> Jennifer S.
> skripac_at_mainstreams.com
Received on Thu Nov 18 1999 - 08:59:56 CST