Re: Result set positioning

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: 6 Jul 2001 07:03:26 -0700
Message-ID: <9i4gfe01cd6@drn.newsguy.com>

In article <3B454E2F.4E33C816_at_exesolutions.com>, "Daniel says...
>
>Francesco Rossi wrote:
>
>> Hi at all,
>> i must select a range of record from a table starting at record position x,
>> example: in a table with 100 records i must start the result set from the
>> 20th record wthout any order, such as SELECT * FROM TABLE NEXT 20.
>> How i can do this with a Select ?
>> Thank
>
>Something tells me this is a test question and you want us to do it for you ...
>but what the heck.
>
>SELECT field1, field2
>FROM (
> SELECT field1, field2, rownum
> FROM mytable)
>WHERE rownum > 19
>AND rownum < 40;
>
>Daniel A. Morgan
>

You need to alias that rownum else the rownum in the outer query is the rownum FOR the outer query, not the inner - you'll get ZERO rows:

tkyte_at_TKYTE901.US.ORACLE.COM> select ename, empno   2 from ( select emp.*, rownum from emp )   3 where rownum >= 5 and rownum <= 10   4 /

no rows selected

A proper technique would be:

tkyte_at_TKYTE901.US.ORACLE.COM> select ename, empno   2 from ( select emp.* , rownum R

  3             from emp
  4                    where rownum <= 10 )

  5 where r >= 5
  6 /

ENAME EMPNO
---------- ----------

MARTIN           7654
BLAKE            7698
CLARK            7782
SCOTT            7788
KING             7839
TURNER           7844

6 rows selected.

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp 

Received on Fri Jul 06 2001 - 09:03:26 CDT