Re: ROWNUM, order by

Hi, Sean.

> 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?
>

Simply, oracle selects one row from the database and the rownum for that row is 1. Then, oracle checks rownum conditions and this is how it looks: where 1>9 and 1<16;  

Of course, this is FALSE and row is not selected. Then, oracle selects the second row and assigns again rownum=1 to that row. And all over again. That is the reason why you get "no rows selected".

> I would like to do this:
>
> select *
> from mytable
> where rownum > 9
> and rownum < 16
> order by c1;
>

This will work:

1.Create view myview as you described.
2. Use this select:
select s.* from
(select rownum r, m.*
 from myview m
 where rownum<16
) s
where s.r >9
and s.r<16;

Regards,
Tanya

--
Tanya Injac
Oracle Developer/DBA
Unisys NZ Received on Mon May 11 1998 - 18:41:43 CDT