Re: Oracle Newbie : Getting only limited number of rows

From: Peter H. Larsen <plarsen_at_dc.dynares.com>
Date: 1998/06/12
Message-ID: <35811E2D.B3572802@dc.dynares.com>#1/1

Hi,
Your initial problem is, that you DON'T want to limit the number of rows processed, but the number of rows returned. Rownum limits the number of rows processed, that's why it does't work the way you want.

The answer to your question is, that SQL*PLUS won't help you there. Noone will, unless you turn to PL/SQL or embedded SQL. In short, what you do is to execute a cursor, and only fetch the number of times you desire. In PL/SQL that might look like:

DECLARE
  CURSOR C1 IS
    SELECT SALESPERSON, SUM(SALES) SALES

      FROM SALES
     GROUP BY SALESPERSON

    ORDER BY SUM(SALES) DESC;
  W1 C1%ROWTYPE;
  I NUMBER := 0;
BEGIN
  OPEN C1;
  DBMS_OUTPUT.PUT_LINE('TOP 10 SALESPERSONS');   LOOP
    FETCH C1 INTO W1;
    EXIT WHEN C1%NOTFOUND OR I >= 10;
    DBMS_OUTPUT.PUT(W1.SALESPERSON);
    DBMS_OUTPUT.PUT_LINE(to_char(w1.sales,' 999,999,999.99'));     I := I + 1;
  END LOOP;
  CLOSE C1;
END; That should do it.

• Peter H. Larsen Oracle Consultant

avaneesh.arora wrote:
>
> HI,
>
> I have the following problem :
>
> I want to select a limited number of rows from a table sorted on a
> particular set of columns. How do I structure my query to get, say Next N
> rows(50/100) based on the specified sort order. Limiting it by ROWNUM limits
> the row count and does not work as desired.
>
> Please help.
> Thanks,
> Avaneesh
Received on Fri Jun 12 1998 - 00:00:00 CDT