Re: Result set positioning

Thomas Kyte wrote:

> 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

You are correct. The rownum must be aliased for it to work.

Daniel A. Morgan Received on Fri Jul 06 2001 - 23:11:10 CDT