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

In article <1994Feb11.004859.25154_at_mnemosyne.cs.du.edu> 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.
>
[example deleted]

Strictly speaking: NO. This type of question is not suitable for SQL. However, in some situations you can choose to rely on Oracle using a certain index to retrieve the rows in the preferred order, and then limit the number of rows returned with "where rownum < ..".

You really need 3GL logic to solve this type of problem. The program would fetch (an array of) rows and process them one at a time and stop when some condition was met. For example, suppose you want to fill a truck with order-items from a warehouse. You must select items up to a certain predefined total weight. This is much harder than your example, but it's still basically the same problem. It can easily be done in PL/SQL:

create or replace procedure packItems
( truck in integer, maxWeight in number ) is

   cursor c is select * from order_items where ... order by ...;    totWeight number;
   done exception;
   truckNotFilled exception;
begin

   lock table order_items in exclusive mode; /* simplifies example */    for item in c loop

      totWeight := totWeight + item.weight;
      if totWeight < maxWeight then
         update order_items set truckId = truck where current of c;
      else
         totWeight := totWeight - item.weight;
         raise done;
      end if;

   when done then

      null;
   when truckNotFilled then

      ...
end;
[There's probably gonna be a couple of syntax errors in this since I haven't bothered testing it]

Generalized: A restriction where each rows fulfillment of the querycondition  is dependant on one or more *other* rows in the same query is not suitable for SQL. [Does anybody understand that ?]

hope this helps,

--

Magnus Lonnroth
Tech.Sales & Consultant
Oracle Sweden
Mail: mloennro_at_se.oracle.com