Re: Limiting amount of rows returned from a query.

Perhaps an analytical function will do the trick? Here some food for thought:

SELECT * FROM (
SELECT v.*,

       row_number() over (ORDER BY 'a') rn FROM dba_source v
) v
WHERE rn < 2001

On Sun, 12 May 2002 02:05:32 +0200, Morten <usenet_at_kikobu.com> wrote:

>
>Hi. Is there a way that I can limit the number of rows returned
>by a query to eg. 2000?
>
>I need to do this due to a bug in Oracle - when the resultset is
>above X rows, the interMedia/Oracle connection core dumps.
>
>The bug is immediate when I use the ORDER BY clause. And I need
>to do that.. And that seems a very big problem. Otherwise I could
>use ROWNUM.
>
>A potential solution is to use the FIRST_ROWS hint. But the
>query has multiple WHERE clauses, so this hint does not work.
>Is there something similar to the below that can be done?
>(Example is from the Oracle Text Tuning Guide).
>
>declare
>cursor c is
>select /*+ FIRST_ROWS */ pk, score(1), col from ctx_tab
>where contains(txt_col, 'test', 1) > 0 order by score(1) desc;
>begin
>for i in c
>loop
>insert into t_s values(i.pk, i.col);
>exit when c%rowcount > 2000;
>end loop;
>end;
>/
>
>Your help is much appreciated,
>
>Morten
>

regards
Marc Blum
mailto:marc_at_marcblum.de
http://www.marcblum.de Received on Sun May 12 2002 - 03:44:57 CDT