Need help on limiting # of rows returned...
Date: Fri, 11 Feb 94 00:48:59 GMT
Message-ID: <1994Feb11.004859.25154_at_mnemosyne.cs.du.edu>
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.
for example,
Let's assume we have 70,000 employees in the emp table and I want to return an arbitrary number (say, 50) to a client app to interactively do some processing of the info...um,..assume all our emps are now moving to a commission based salary and we want to set the initial commission level for each emp. Also assume that I, for some god-awful reason have to process them in order, lowest empno's first, and assume that emps have to be processed individually (I know, I know, this is a lousy example!!)
The query...
select empno, name, deptno, comm
from emp
where comm is null
and rownum < 51
order by empno;
...will get the _first_ 50 rows that meets the 'where comm is null' and then order the result. I need the reverse...order the result and then limit the return to 50...as in, peel the next 50 from the index.
I don't want to return all 70,000 and then access via 'fetch next 50', 'cause the initial query takes so long. I can only think to re-create a table of emps sorted on empno, and let this non-arbritrary ordering of rows return the empno's in order. Is there another way?
Thanks,
-- Bob Alimi | balimi_at_nyx.cs.du.edu Sacramento, CA |Received on Fri Feb 11 1994 - 01:48:59 CET