Re: Oracle Newbie : Getting only limited number of rows
Date: 1998/06/12
Message-ID: <35811E2D.B3572802_at_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 CEST