[Opensource] Has anyone used the limitation syntax with Oracle ?

[Giovanni Azua]

hi,

For retrieving the Nth X rows with Oracle you have to:

SELECT *
FROM ( SELECT A.*, ROWNUM rnum
       FROM ( YOUR_QUERY_GOES_HERE ) A
       WHERE ROWNUM <= :upper_bound_row )
 where rnum >= :lower_bound_row;

a bit tricky, Thomas Kyte gave me the hint just today :-)
This solution works for either views or tables in Oracle.

The other way around, works only with tables, simple views
and snapshots (materialized views) and is quite more
inefficient, note the ROWID and minus op:

 SELECT *
        FROM   tableX
        WHERE  ROWID in (
           SELECT ROWID FROM tableX
           WHERE ROWNUM <= 7
          MINUS
           SELECT ROWID FROM tableX
           WHERE ROWNUM < 5);

Hope this helps,
Giovanni

----- Original Message -----
From: "Michael Rimov" <rimovm at centercomp.com>
To: <opensource at jcorporate.com>
Sent: Friday, May 31, 2002 11:29 PM
Subject: Re: [Opensource] Has anyone used the limitation syntax with Oracle
?


> At 04:52 PM 5/31/2002 +0200, you wrote:
> >Has anyone used the limitation syntax with Oracle ?
> >
> >It doesn't work as explained in the DBObject source.
> >
> >For instance : SELECT * FROM TABLE WHERE ROWNUM>10 AND ROWNUM<20  returns
> >       no rows.
> >
> >May be because the SQL resultset is not ready when the rownum expression
is
> >       processed ...
>
> What is the normal syntax required in Oracle to allow for the limitation?
> Perhaps this is where the problem lies.
>
>                                                  -Mike
>
> _______________________________________________
> Opensource mailing list
> Opensource at jcorporate.com
> http://mail.jcorporate.com/mailman/listinfo/opensource
> Archives: http://mail.jcorporate.com/pipermail/opensource/
>