Re: LIMITing output

I was able to accomplish tis using PL/SQL tables- like...

declare
i number := 0;
cursor c1 is select ename, salary from my_table order by salary desc; type t_c1 is table of c1%rowtype index by binary_integer; v_c1 t_c1;
--you now have an 2 dimensional array with a counter

begin
for x in c1 loop

    v_c1(i).ename := x.ename;
    v_c1(i).salary := salary;
i := i + 1;
end loop
--you now have an array that is ordered by salary descending

--to get the first 20 items sorted by salary i := 1;
while i <21 loop

   --some code here that places the first 20 rows on your web page end;

--if you want items 21 to 40 then use this while loop but set i to 20 before you start

Hope this helps!

Bernard Polarski <bpolarsk_at_yahoo.com> wrote in message news:88s5rt$j1l$1_at_nnrp1.deja.com...
> Hello,
>
> I have already had this problem. Here is what Oracle says :
> (From online generic documentation 7.3.4)
>
> <cut>
> DECLARE
> CURSOR c1 IS SELECT empno, sal FROM emp
> WHERE sal > 2000 AND ROWNUM < 10; -- returns 10 rows
>
> The value of ROWNUM increases only when a row is retrieved, so the only
> meaningful use of ROWNUM in a WHERE clause is
>
> ... WHERE ROWNUM < constant;
>
> For example, the following condition cannot be met because the first
> nine rows are never retrieved:
>
> ... WHERE ROWNUM = 10;
> </cut>
>
> Effectively if I try :
> =======================
>
> SQL> select rownum,poll, GROUPNAME from poll;
>
> ROWNUM POLL GROUPNAME
> ---------- ---------- ---------------
> 1 3 A
> 2 4 B
> 3 5 C
> 4 6
> 5 7 D
> 6 8 E
> 7 10
> 8 11 F
> 9 12
> 10 13
> 11 14 G
> 12 15
> 13 16
> 14 17
> 15 18
>
>
> Then :
>
> SQL> select rownum,poll, GROUPNAME from poll where rownum > 5 ;
>
> no rows selected
>
> But we can use a trick :
>
> 1 select rownum,poll, GROUPNAME from poll
> group by rownum, poll,groupname
> having rownum >5 and rownum < 10
> SQL> /
>
> ROWNUM POLL GROUPNAME
> ---------- ---------- ---------------
> 6 8
> 7 10
> 8 11
> 9 12
>
>
> In Mysql there is the extremely usefull 'limit 10,20' clauses. I never
> found the equivalent in Oracle.
>
> If somebody knows a better solution, do not hesitate.
>
> B. Polarski
>
> In article <38B16EEF.A26CB1C4_at_fast.no>,
> kev <kevin.porter_at_fast.no> wrote:
> > Hi,
> >
> > I've been trying to find out for a while how to select the Nth M rows
> > from a resultset, for the purpose of paginating results on a web page,
> > eg show news stories 1-10 on the first page, user clicks 'More', gets
> > news stories 11-20, etc.
> >
> > I never found a way to do it in Oracle 8.0.5 (mainly because of
> > ORDER BY clauses not being allowed in subqueries) but I was told that
> 8i
> > would be better for this because it does allow ORDER BY clauses in
> > subqueries. I've upgraded to 8i now (on Red Hat 6.1) and a quick test
> > failed to show that ORDER BY clauses in subqueries are indeed
> supported.
> >
> > How do you people usually tackle the problem of paginating results on
> > web pages? Does 8i indeed support ORDER BY clauses in subqueries?
> >
> > TIA,
> >
> > - Kev
> >
> >
>
> --
> B.Polarski
>
> http://www.geocities.com/bpolarsk
> Email : bpolarsk_at_yahoo.com
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
Received on Tue Feb 22 2000 - 01:25:28 CST