Re: ROWNUM, order by

Rownum is a number generated for each row in the result set. Rownum is assigned to the row as it is selected. So as the first row is selected it is assigned a rownum of one. Since it does not meet the criteria of rownum
> 9 the row is then excluded from the result set. The next row's rownum
will still be one since the previous row was not part of the result set. So, the next row is fetched and assigned a rownum of one. This will end with the no rows selected message you have been getting.

Hope this helps.

Patrick Flahan
flahan_at_leading.net

Sean Hull wrote in message ...
>
>Hello all:
>
>I would like to do this:
>
>select *
>from mytable
>where rownum > 9
>and rownum < 16
>order by c1;
>
>I know I have to create a view because the rownum is assigned before the
>order by clause is hit.
>
>create or replace view myview as
>select *
>from mytable
>group by c1;
>
>But then only this works:
>
>select * from myview where rownum < 10;
>
>If I want rows 10-15 as above and I do:
>
>select * from myview where rownum > 9 and rownum < 16;
>
>I always get "no rows selected". WHY? I tried
>
>select rownum,c1,c2 from myview;
>
>and everything looks ok. Also, this all seems reasonable since this
>works:
>
>select * from myview where c1 > 10 and c1 < 20;
>
>What gives?
>
>Sean
>
>
>
Received on Tue May 12 1998 - 20:47:17 CDT