Re: LIMITing output

From: Bernard Polarski <bpolarsk_at_yahoo.com>
Date: Mon, 21 Feb 2000 20:06:25 GMT
Message-ID: <88s5rt$j1l$1@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 Mon Feb 21 2000 - 14:06:25 CST