Re: Top 'N' Analysis

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 25 Sep 2003 13:06:33 GMT
Message-ID: <tLBcb.122661$bo1.43832@news-server.bigpond.net.au>

<fleeting_at_net1plus.com> wrote in message news:05u3nvcvrvq1rkr42e72ssvpmfnf20rmaf_at_4ax.com...
> I'm studying to get my DBA in Oracle 9i. After I went through the 3
> CD's sent to me, I took the first test and found a question that I
> never saw on the CD's! It refered to Top 'N' Analysis.
>
> I tried going through the CD's again just to see if maybe I missed
> something, but still cannot find the reference to Top 'N' analysis.
>
> Can someone give me a head's-up as to what this is? Even better - can
> you tell me where on the three CD's I may have missed this?
>
> Thank you for your help - great group!
>
> Kevin

Hi Kevin,

Top (or Bottom) N Analysis is a neat method that allows one to relatively easily and efficiently determine the first or last "N" rows in an ordered returned row set. For example:

SELECT *
FROM (SELECT bowie_album, bowie_sales

              FROM bowie_stuff
              ORDER BY bowie_sales DESC)

WHERE rownum < 11;

The in-line view is "clever" enough to realize that only 10 rows are required (due to the outer WHERE condition) and so only stores to "top-n" rows as it ploughs through the data, resulting in a tiny sort at the end.

To get the bottom-n analysis, you obvious sort the data in ascending order.

Hope this helps

Richard Received on Thu Sep 25 2003 - 08:06:33 CDT