Re: SELECT second 25 records

From: Steven Franklin <steven.franklin_at_wcom.com>
Date: Wed, 04 Aug 1999 15:47:15 GMT
Message-ID: <37A86082.F982EA79@wcom.com>

Khal wrote:

> I'm looking for a way to select the second group of 25 matches in a select
> statement.
>
> Any Ideas?
>
> Khal

Well, it depends. How big is the data set? Are you going to want to find then next 25 rows, and the next 25 rows, ... ? Does order matter? Does the data change? Or is it static?

For a small data set use rownum and nesting, i.e.   SELECT col1,col2
  FROM (SELECT col1,col2,rownum row_no

                FROM tbl
                WHERE rownum < m)

  WHERE row_no BETWEEN n AND m;

If order matters this doesn't work (at least on Oracle 7). If your data set is large this gets very slow. I believe that Oracle 8 allows you to include an 'ORDER BY' clause in the nested SELECT, so you may be able to define order that way. I can't try this since I'm using Oracle 7 here.

For a larger data set you probably want to put the results of the query into a 'results table,' something like this,

results (
  viewid NUMBER,
  row_no NUMBER,
  row_id ROWID
)

using for example,
INSERT INTO results (SELECT :viewid, seq.nextval,rowid FROM <your query>);

Now retrieving the 25 rows between n and m is easy and quick using the ROWID and a join,

SELECT col1,col2
FROM results,tbl
WHERE (tbl.rowid = results.row_id) AND

               (viewid = :viewid) AND
               (row_no BETWEEN n AND m);

If your data is dynamic then you may need to define an order to ensure that new records are added at the end of the query, so that you can minimize updates to queries against the end of the result set. Received on Wed Aug 04 1999 - 10:47:15 CDT