Re: Maxrows in select

From: Billy Verreynne <verreyb_at_telkom.co.za>
Date: Thu, 15 Aug 2002 12:20:07 +0200
Message-ID: <ajfv8n$48t$1@ctb-nnrp2.saix.net>

Stein Andersen wrote:

> How do I set the maximum number of rows to receive in an select, and is it
> possible to retrieve the next set of data (without using cursors and
> pl/sql) if maximum number of rows is exceeded.

With difficulty. And nested selects.

Consider.

SELECT
  *
FROM
  ( SELECT

       rownum  ROW_NUM,
       s.*

    FROM
    ( SELECT

        customer_id,
        count(*)          INVOICES,
        sum(total_amount) AMOUNT
      FROM foobar.invoices
      GROUP BY customer_id
      ORDER BY 3 DESC
     ) s

   )
WHERE row_num BETWEEN 10 and 19

From the inside out.

Inner select is the query. It does the grouping and ordering of the resulting data set (in the above example, it lists the customers with the highest invoice totals first).

The next select adds a row number to the data set.

The outer select specifies which rows from the resulting data set to display. Replace 10 and 19 with the start and end row numbers for the page that the user wants to see.

Note that the complete data set will be rebuild each and every time the user pages through the results. This is the price you pay for not using a cursor to page through. Received on Thu Aug 15 2002 - 05:20:07 CDT