Re: Need help on limiting # of rows returned...

From: Michael P. Stein <mstein_at_access1.digex.net>
Date: 11 Feb 1994 16:28:33 -0500
Message-ID: <2jgta1$45i_at_access1.digex.net>


Magnus Lonnroth <mloennro_at_se.oracle.com> wrote:
>balimi_at_nyx.cs.du.edu (bob alimi) writes:
> > Is there _any_ way to horizontally partition fetches from a table? I
> > need to retrieve a subset of rows from a table and the rows must
> > be ordered.
>Strictly speaking: NO. This type of question is not suitable for SQL.

    Strictly speaking: YES. However, you may find it so slow that you might still conclude that it is not suitable for SQL. :)

    SELECT ... FROM SOME_TABLE T
    WHERE <max_nr_of_rows_to_retrieve> >=

        ( SELECT COUNT(*) FROM SOME_TABLE
          WHERE <unique sort key> <= T.<unique sort key> )
    ORDER BY <sort key>;

The subquery returns the ordinal of the candidate row in the specified sort scheme. Thus the rows retured are the ones whose ordinals are less than the desired maximum.

    You could even get, e.g., rows 11-20 in the desired ordering via:

    SELECT ... FROM SOME_TABLE T
    WHERE 20 >= ( SELECT COUNT(*) FROM SOME_TABLE

                  WHERE <unique sort key> <= T.<unique sort key> )
      AND 10 < ( SELECT COUNT(*) FROM SOME_TABLE
                 WHERE <unique sort key> <= T.<unique sort key> )
    ORDER BY <sort key>;

The sort condition used in the WHERE clause must compare unique keys to guarantee exactness of counts. If all else fails, add ROWID to the mix:

    ... ( ... WHERE <key> < T.<key> or

                    ( <key> = T.<key> and rowid <= T.<rowid> ) ) ...

    Like I said, though, if you don't have an index on the sort key, you might as well go to lunch if the table is large.

-- 
Mike Stein			The above represents the Absolute Truth.
POB 10420			Therefore it cannot possibly be the official
Arlington, VA  22210		position of my employer.
Received on Fri Feb 11 1994 - 22:28:33 CET

Original text of this message