Re: SQL - SELECT x rows...

From: Mark G <markag_at_wonderstats.com>
Date: 2000/08/09
Message-ID: <F4155D9F9AA4D111B9990020AFBA52D53E7D56@class06.ip061.sfi-software.com>#1/1

From Oracle:

Optimized "Top-N" Analysis
Top-N queries ask for the n largest or smallest values of a column. An example is "What are the top ten best selling products in the U.S.?" Of course, we may also want to ask "What are the 10 worst selling products?" Both largest-values and smallest-values sets are considered Top-N queries.

Details
Top-N queries use a consistent nested query structure with the elements described below.

Subquery to generate the sorted list of data. The subquery includes the ORDER BY clause to ensure that the ranking is in the desired order. For results retrieving the largest values, a DESC parameter is needed.

Outer Query to limit the number of rows in the final result set. The outer query includes:

ROWNUM pseudo-column which assigns a sequential value starting with 1 to each of the rows returned from the subquery.

WHERE clause used to specify the n returned rows. The outer WHERE clause must use a "<" or "<=" operator.

The high-level structure of these queries is:

SELECT column_list ROWNUM FROM
  (SELECT column_list FROM table
   ORDER BY Top-N_column)
WHERE ROWNUM <= N

Examples
To illustrate the concepts here, we extend the scenario used in our earlier examples. We will now access the name of the sales representative associated with each sale, stored in the "name" column. and the sales commission earned on every sale. The SQL below returns the top 10 sales representatives ordered by dollar sales, with sample data shown in Table 20-9:

select ROWNUM AS Rank, Name, Region, Sales from   (select Name, Region, sum(Sales) AS Sales

      from Sales GROUP BY Name, Region
      order by sum(Sales) DESC)

WHERE ROWNUM <= 10

"Joseph Ranseth" <jransethNO_SPAM_at_worldcupfishing.com> wrote in message news:BKgk5.14$Lc.1764_at_news1.mts.net...
> How can I select 'x' number of rows from/in a query?
>
> ie: I want to select the 20 oldest employees, or 100 highest paid, etc.
>
> Is there a simple way of going about this, or do I have to select all of
 the
> records and then only print the desired amount (That wouldn't be
 efficient,
> though, would it?)
>
> Thanks in Advance,
>
> JR
>
Received on Wed Aug 09 2000 - 00:00:00 CDT